vlookup linked spreadsheet update issue

I have a spreadsheet that uses vlookup to get values for the cells from a master spreadsheet or return "NM" if there is no value for the search criteria. If I go back in an d open the spreadsheet, and when prompted, I say "Don't Update" it goes ahead and clears all the values and replaces them with "NM". Is that a function of using the following formula?

"=IFERROR(VLOOKUP($A23,OFFSET('[Effluent Tables.xlsx]effluent'!$D$1,MATCH(E$1,'[Effluent Tables.xlsx]effluent'!$D$2:$D$5758,0),1,COUNTIF('[Effluent Tables.xlsx]effluent'!$D$2:$D$5758,E$1),17),17,0),"NM")"

Thanks,
John

If you don't update the links, they will return an error. Your formula basically says:

=IFERROR( ... , "NM")

so yes, you will get NM for all those formulae if you don't update the links.

Hope this helps.

Pete

Thanks - so what would I do so that it holds the data in the cells when I reopen, and allows me to still do automatic calculations (I imagine that it won't...). It would be nice not to have to have all the spreadsheets open at once.

TIA,
John

The way you have written the formula, i.e. without the full path to the workbook Effluent Tables.xlsx, it implies that the workbook is open in the same instance of Excel, so if you don't open it first that's why you get the error, which in turn will give you "NM". I'm not sure why you are using OFFSET, as this is a volatile function which means that it will calculate everytime there is a change to the worksheet. Can't you just have a straightforward link to the table in that workbook?

Hope this helps.

Pete

Pete,

The formula actually has the full path in it:

"=IFERROR(VLOOKUP($A12,OFFSET('N:\Projects\path\[Effluent Tables.xlsx]effluent'!$D$1,MATCH(G$1,'N:\Projects\path\[Effluent Tables.xlsx]effluent'!$D$2:$D$5758,0),1,COUNTIF('N:\Projects\path\[Effluent Tables.xlsx]effluent'!$D$2:$D$5758,G$1),17),17,0),"NM")"

If I update the file or open the master spreadsheet, then the path goes away.

I am kinda a newbie to to functions like vlookup. I needed a way to search for two different key values in the master spreadsheet - I have the data sorted by component and then sample ID, so it searches for the sample ID (referenced cell in column A) after it finds the block of data that contains the component (referenced cell in row 1) and then pulls the value. If there was no measurement taken then it returns a value of "NM". Is there some other way to do this? Basically the master spreadsheet has three columns of interest

Thanks

I can understand what you are saying, and can more-or-less visualise how you have arranged your data. There may be other ways of doing this, eg by using a helper column which just joins the component and sample ID together and then you could do a lookup on that (or an INDEX/MATCH, depending on where the helper column is). I try to avoid using OFFSET because it is volatile and thus causes your workbook to recalculate (slowing it down).

Hope this helps.

Pete

Pete - that is great! I will try that and let you know. I have already added a column to my master spreadsheet so i can just use that instead.