vlookup linked spreadsheet update issue

[geckoman]

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

[Pete_UK]

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

[geckoman]

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

[Pete_UK]

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

[geckoman]

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

[Pete_UK]

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

[geckoman]

So it sounds like I could use the "CONCATENATE" function to make this "helper" column.

But would that get a bit clunky for my data set? What I have is some lab data - so samples get analyzed for a bunch of different components and there is a bunch of other information that is provided by the lab. There are two different component lists, one shorter than the other, so for each sample I may have 12 or maybe 20 components (rows). So I now make an array where the rows are the different samples and going across in the columns are all the components. Can I set it up that my vlookup is a combination of the sample ID and component? something like "vlookup("$A12 G$1",arguments..." I am not sure what the syntax would be if I could do it this way...

Thanks!

[Pete_UK]

Bear in mind that I haven't seen your files, but to create the helper column in the file Effluent_tables.xlsx it will be easier to insert a new column A so other columns get shifted over, and then you can put this formula in A2:

=B2&E2

(or whatever columns you are using for the component and sample ID), and copy this down as far as you need to (even beyond your data, so that you can cope with extra data to be added). This column can then be hidden so that the sheet looks as it did before.

Then in your VLOOKUP formula you can just have something like:

=VLOOKUP(A23&E23,'path[filename.xls]sheetname'!$A:$R,18,0)

So, you have already concatenated the two values in the other workbook and now you concatenate the lookup values within the VLOOKUP formula.

Hope this helps.

Pete

[geckoman]

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.

[geckoman]

Pete - that is so much more elegant and easy - thanks - works like a charm!