software

I have a rather large workbook with multiple uses of LOOKUP over a variety of
worksheets. In some cases, the LOOKUP of a particular value occurs only on
one worksheet, in other cases LOOKUP of the same value may occur over several
spreadsheets.

The problem I've run in to today is that when the data is updated, the value
returned by LOOKUP may not be correct (it keeps the old value and doesn't
replace it with the new one). In this particular instance, I used LOOKUP on
two different worksheets, both looking up values from the same table; some of
the values being looked up were the same, some were different. Spreadsheet 1
updated to the new values when the data was changed, however Spreadsheet 2
did not and kept the old value (which is now incorrect).

I was able to correct it by quot;re-doingquot; the LOOKUP formula on Spreadsheet 2,
but that's not going to be very effective long term.

Any suggestions of what the problem might be?
--
Linda Peters

lt;Any suggestions of what the problem might be?gt;

Not without your formulas, input values, expected and actual results!

--
Kind regards,

Niek Otten

quot;Linda Petersquot; gt; wrote in message
...
gt;I have a rather large workbook with multiple uses of LOOKUP over a variety
gt;of
gt; worksheets. In some cases, the LOOKUP of a particular value occurs only on
gt; one worksheet, in other cases LOOKUP of the same value may occur over
gt; several
gt; spreadsheets.
gt;
gt; The problem I've run in to today is that when the data is updated, the
gt; value
gt; returned by LOOKUP may not be correct (it keeps the old value and doesn't
gt; replace it with the new one). In this particular instance, I used LOOKUP
gt; on
gt; two different worksheets, both looking up values from the same table; some
gt; of
gt; the values being looked up were the same, some were different. Spreadsheet
gt; 1
gt; updated to the new values when the data was changed, however Spreadsheet 2
gt; did not and kept the old value (which is now incorrect).
gt;
gt; I was able to correct it by quot;re-doingquot; the LOOKUP formula on Spreadsheet
gt; 2,
gt; but that's not going to be very effective long term.
gt;
gt; Any suggestions of what the problem might be?
gt; --
gt; Linda Peters
Formula from spreadsheet 1: =LOOKUP(A27,Data!A:A,Data!L:L)
Expected result: 18165
Actual result: 18165

Formula from spreadsheet 2: =LOOKUP(A80,Data!A:A,Data!L:L)
Expected result: 18165
Actual result: 13665 (this is the old result, prior to updating the data)

In both cases, the value being looked up (in A27 amp; A80) are the same value
(an accounting code: 1-102-342-205-110)

--
Linda Petersquot;Niek Ottenquot; wrote:

gt; lt;Any suggestions of what the problem might be?gt;
gt;
gt; Not without your formulas, input values, expected and actual results!
gt;
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt; quot;Linda Petersquot; gt; wrote in message
gt; ...
gt; gt;I have a rather large workbook with multiple uses of LOOKUP over a variety
gt; gt;of
gt; gt; worksheets. In some cases, the LOOKUP of a particular value occurs only on
gt; gt; one worksheet, in other cases LOOKUP of the same value may occur over
gt; gt; several
gt; gt; spreadsheets.
gt; gt;
gt; gt; The problem I've run in to today is that when the data is updated, the
gt; gt; value
gt; gt; returned by LOOKUP may not be correct (it keeps the old value and doesn't
gt; gt; replace it with the new one). In this particular instance, I used LOOKUP
gt; gt; on
gt; gt; two different worksheets, both looking up values from the same table; some
gt; gt; of
gt; gt; the values being looked up were the same, some were different. Spreadsheet
gt; gt; 1
gt; gt; updated to the new values when the data was changed, however Spreadsheet 2
gt; gt; did not and kept the old value (which is now incorrect).
gt; gt;
gt; gt; I was able to correct it by quot;re-doingquot; the LOOKUP formula on Spreadsheet
gt; gt; 2,
gt; gt; but that's not going to be very effective long term.
gt; gt;
gt; gt; Any suggestions of what the problem might be?
gt; gt; --
gt; gt; Linda Peters
gt;
gt;
gt;

If you do Tools / options / calculation, does it say automatic?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------quot;Linda Petersquot; gt; wrote in message
...
gt; Formula from spreadsheet 1: =LOOKUP(A27,Data!A:A,Data!L:L)
gt; Expected result: 18165
gt; Actual result: 18165
gt;
gt; Formula from spreadsheet 2: =LOOKUP(A80,Data!A:A,Data!L:L)
gt; Expected result: 18165
gt; Actual result: 13665 (this is the old result, prior to updating the data)
gt;
gt; In both cases, the value being looked up (in A27 amp; A80) are the same value
gt; (an accounting code: 1-102-342-205-110)
gt;
gt; --
gt; Linda Peters
gt;
gt;
gt; quot;Niek Ottenquot; wrote:
gt;
gt;gt; lt;Any suggestions of what the problem might be?gt;
gt;gt;
gt;gt; Not without your formulas, input values, expected and actual results!
gt;gt;
gt;gt; --
gt;gt; Kind regards,
gt;gt;
gt;gt; Niek Otten
gt;gt;
gt;gt; quot;Linda Petersquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I have a rather large workbook with multiple uses of LOOKUP over a
gt;gt; gt;variety
gt;gt; gt;of
gt;gt; gt; worksheets. In some cases, the LOOKUP of a particular value occurs only
gt;gt; gt; on
gt;gt; gt; one worksheet, in other cases LOOKUP of the same value may occur over
gt;gt; gt; several
gt;gt; gt; spreadsheets.
gt;gt; gt;
gt;gt; gt; The problem I've run in to today is that when the data is updated, the
gt;gt; gt; value
gt;gt; gt; returned by LOOKUP may not be correct (it keeps the old value and
gt;gt; gt; doesn't
gt;gt; gt; replace it with the new one). In this particular instance, I used
gt;gt; gt; LOOKUP
gt;gt; gt; on
gt;gt; gt; two different worksheets, both looking up values from the same table;
gt;gt; gt; some
gt;gt; gt; of
gt;gt; gt; the values being looked up were the same, some were different.
gt;gt; gt; Spreadsheet
gt;gt; gt; 1
gt;gt; gt; updated to the new values when the data was changed, however
gt;gt; gt; Spreadsheet 2
gt;gt; gt; did not and kept the old value (which is now incorrect).
gt;gt; gt;
gt;gt; gt; I was able to correct it by quot;re-doingquot; the LOOKUP formula on
gt;gt; gt; Spreadsheet
gt;gt; gt; 2,
gt;gt; gt; but that's not going to be very effective long term.
gt;gt; gt;
gt;gt; gt; Any suggestions of what the problem might be?
gt;gt; gt; --
gt;gt; gt; Linda Peters
gt;gt;
gt;gt;
gt;gt;
lt;(this is the old result, prior to updating the data)gt;

Which data are you updating; A:A?

LOOKUP requires that the data is sorted ascending. Is that condition
satisfied?

LOOKUP is not used a lot anymore. As HELP says, it is provided for backward
compatibility.
Normally, one would use VLOOKUP.

BTW, did you check Toolsgt;Optionsgt;Calculation Tab and make sure Calculation
is Automatic?--
Kind regards,

Niek Otten
quot;Linda Petersquot; gt; wrote in message
...
gt; Formula from spreadsheet 1: =LOOKUP(A27,Data!A:A,Data!L:L)
gt; Expected result: 18165
gt; Actual result: 18165
gt;
gt; Formula from spreadsheet 2: =LOOKUP(A80,Data!A:A,Data!L:L)
gt; Expected result: 18165
gt; Actual result: 13665 (this is the old result, prior to updating the data)
gt;
gt; In both cases, the value being looked up (in A27 amp; A80) are the same value
gt; (an accounting code: 1-102-342-205-110)
gt;
gt; --
gt; Linda Peters
gt;
gt;
gt; quot;Niek Ottenquot; wrote:
gt;
gt;gt; lt;Any suggestions of what the problem might be?gt;
gt;gt;
gt;gt; Not without your formulas, input values, expected and actual results!
gt;gt;
gt;gt; --
gt;gt; Kind regards,
gt;gt;
gt;gt; Niek Otten
gt;gt;
gt;gt; quot;Linda Petersquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I have a rather large workbook with multiple uses of LOOKUP over a
gt;gt; gt;variety
gt;gt; gt;of
gt;gt; gt; worksheets. In some cases, the LOOKUP of a particular value occurs only
gt;gt; gt; on
gt;gt; gt; one worksheet, in other cases LOOKUP of the same value may occur over
gt;gt; gt; several
gt;gt; gt; spreadsheets.
gt;gt; gt;
gt;gt; gt; The problem I've run in to today is that when the data is updated, the
gt;gt; gt; value
gt;gt; gt; returned by LOOKUP may not be correct (it keeps the old value and
gt;gt; gt; doesn't
gt;gt; gt; replace it with the new one). In this particular instance, I used
gt;gt; gt; LOOKUP
gt;gt; gt; on
gt;gt; gt; two different worksheets, both looking up values from the same table;
gt;gt; gt; some
gt;gt; gt; of
gt;gt; gt; the values being looked up were the same, some were different.
gt;gt; gt; Spreadsheet
gt;gt; gt; 1
gt;gt; gt; updated to the new values when the data was changed, however
gt;gt; gt; Spreadsheet 2
gt;gt; gt; did not and kept the old value (which is now incorrect).
gt;gt; gt;
gt;gt; gt; I was able to correct it by quot;re-doingquot; the LOOKUP formula on
gt;gt; gt; Spreadsheet
gt;gt; gt; 2,
gt;gt; gt; but that's not going to be very effective long term.
gt;gt; gt;
gt;gt; gt; Any suggestions of what the problem might be?
gt;gt; gt; --
gt;gt; gt; Linda Peters
gt;gt;
gt;gt;
gt;gt;