Sometimes my VLOOKUP values change in an open spreadsheet when I open another
spreadsheet with similar VLOOKUP formulas. If I close the spreadsheet and
save the changes without noticing it can jack my report up pretty good.

Theres several other analyst here whove run into that from time to time,
but no one knows whats going on or how to fix it.

Has anyone had a similar experience or know how to avoid that?

Are you using VLOOKUP to access data from another workbook? This may be
your problem. As I understand it, the VLOOKUP updates only when the other
workbooks are open. Depending on what you want, you may want to consider
using INDIRECT_EXT. It will return values in closed workbooks.

http://xcell05.free.fr/english/moref...direct.ext.htm


"Preston Nuckols" <Preston wrote in
message ...
Sometimes my VLOOKUP values change in an open spreadsheet when I open
another
spreadsheet with similar VLOOKUP formulas. If I close the spreadsheet and
save the changes without noticing it can jack my report up pretty good.

There's several other analyst here who've run into that from time to time,
but no one knows what's going on or how to fix it.

Has anyone had a similar experience or know how to avoid that?

Thanks for the response.

The VLOOKUP values only revert to erroneous data when an archive spreadsheet
with similar VLOOKUP formulas is opened.

For example, we have an €œInventory count€� spreadsheet that we update
everyday. Its linked to a spreadsheet that is replaced daily that shows the
€œOn hand€� quantities of each item.

Each month the €œInventory count€� spreadsheet is archived and named according
to the month it is archived, for example, €œJuly Inventory count€�, €œAug
Inventory count€� etc.

If we have a current €œInventory count€� spreadsheet open and then open an
archived spreadsheet, i.e. the €œAug Inventory count€� spreadsheet, the current
€œInventory count€� spreadsheets VLOOKUP values revert to the now opened €œAug
Inventory count€� spreadsheets values, reflecting the €œOn Hand€� quantities in
Aug.

Note: the VLOOKUP cells in the €œAug Inventory count€� spreadsheet are still
active and the values reflect the inventory levels on the last day of that
particular month, in this case August.

Also: On both spreadsheets, the current €œInventory count€� and €œAugust
Inventory count€� spreadsheets are looking for values in a spreadsheet named
€œOn Hand€�. (We keep the €œOn Hand€� spreadsheet named the same each month.)

So far weve found if we €œbreak€� the links in the archived spreadsheets it
seems to solve the problem.

Any other ideas would be appreciated.


"Barb Reinhardt" wrote:

Are you using VLOOKUP to access data from another workbook? This may be
your problem. As I understand it, the VLOOKUP updates only when the other
workbooks are open. Depending on what you want, you may want to consider
using INDIRECT_EXT. It will return values in closed workbooks.

http://xcell05.free.fr/english/moref...direct.ext.htm


"Preston Nuckols" <Preston wrote in
message ...
Sometimes my VLOOKUP values change in an open spreadsheet when I open
another
spreadsheet with similar VLOOKUP formulas. If I close the spreadsheet and
save the changes without noticing it can jack my report up pretty good.

There's several other analyst here who've run into that from time to time,
but no one knows what's going on or how to fix it.

Has anyone had a similar experience or know how to avoid that?