|This is something I have not seen before. I tried it twice to convince myself and it happened both times. It's way up there on the wierdness scale.|
I created a workbook called FebTest.xls.
I renamed 2 sheets to be Jan and Feb.
I put a lookup_array in A1:B3 of both sheets.
I saved and closed the file.
In a new workbook, I created a lookup_array in A10:B12 to pull the month from a list (per Grok) and put this formula in A14:
=VLOOKUP(2, $A$10:$B$12, 2, 0) which evaluated to Feb.
So far so good.
I then put this in A15:
=VLOOKUP(A1, A14!$A$1:$B$3, 2, 0)
When I hit enter, Excel popped up the window entitled:
Update Values: A15
which was populated with the files in My Documents.
I clicked on FebText.xls which opened a window showing the 3 sheets in FebTest.xls. I clicked on Feb and the formula updated itself to read:
The VLOOKUP then returned the value from the VLOOKUP in the closed file. If I changed the value in A1, the VLOOKUP worked just fine, pulling the correct value from Column 2 or dislaying #N/A if the value from A1 wasn't found..
But wait...there's more.
If I changed the VLOOKUP in A14 to pull Jan from the lookup_array it did not update
That formula just kept pulling data from the Feb sheet of FebTest.xls, so this process will not work to update sheet name via the other VLOOKUP.
But wait...there's even more!
I wanted to see if the formula was really pulling values from the closed workbook. I opened FebTest.xls and change the values in Column 2 of the lookup_array and saved/closed the file.
When I changed the entry in A1...ready?...I still got the old values! Changing A1 continually pulled in the original values that were in the Feb worksheet, not my updated values.
That tells me that the values from the [FebTest]Feb!lookup_array were being stored in the workbook with the VLOOKUP and it wasn't pulling data from the closed workbook.
It wasn't until I physically edited the VLOOKUP formula, at which time it popped up the Update Values: A15 again. I browsed to FebTest.xls, clicked on the Feb sheet and the new value from the [FebTest]Feb!lookup_array appeared in the cell.
That once again tells me that the formula is not reading the closed workbook, but actually storing the values from the [FebTest]Feb!lookup_array inside the workbook and accessing it that way.