Reference Tab in Vlookup from list

[Lauren Thomas]

Hello,

I am currently using the below formula to look up the information I require from my master file.

=VLOOKUP(A4,'[FR Income & Expenditure.xlsx]FR'!$A$5:$H$15,2,FALSE)

As you can see the information is retrieved from the 'FR' tab.

The problem I have is that the data on my master file is likely to move and be placed onto different tabs.

I therefore thought that if I can amend the formula in column E each time I move data on the master file then I would only need to update column E instead of all of the formulae. I want to amend the vlookup to retrieve the data from whichever tab I reference in column E.




Do you know if this can be done?

Thank you
Lauren

 

[Hui]

either
=VLOOKUP(A4, INDIRECT(E4&"!$A$5:$H$15"),2,FALSE)
or
=VLOOKUP(A4, INDIRECT("'[FR Income & Expenditure.xlsx]" & E4 & "'!$A$5:$H$15"), 2, FALSE)

 

[Lauren Thomas]

Doesn't appear to be doing the trick!

 

[GraH - Guido]

Lauren, does it works when the master file is open?
Do mind the single (') and double quotes (").

 

[Lauren Thomas]

It's not working when the master file is open. What about the single and double quotes I don't think I understand!

 

[Chihiro]

FYI - Indirect can't be used on closed workbook. On open workbook, Hui's formula works. Make sure you have copied the formula exactly.

If you need to keep the referenced workbook closed... Depending on version of Excel, you may try other options.

1. PowerQuery - If you have access to it, easiest to use and maintain. UI is by far best out of all options.

2. MS Query - While UI is outdated and you need to know some SQL, this tool is available in most versions of Excel currently in use.

3. VBA (ADO) - Requires knowledge of VBA, ADO, SQL and array object etc. Most flexible if you know what you are doing, but most difficult to manage if you don't. No UI except for coding.

4. VBA (Formula) - Construct formula string via VBA. Using tab/sheet name as variable.

etc.

 

[GraH - Guido]

It's not working when the master file is open. What about the single and double quotes I don't think I understand!

Like this: VLOOKUP(A4, INDIRECT("'[FR Income & Expenditure.xlsx]" & E4 & "'!$A$5:$H$15"), 2, FALSE)
+ What Chihiro says about Power Query.