Excel 2010 VLOOKUP Use cell to reference worksheet in another workbook

[PWD]

Good afternoon, and happy Independence Day to our friends across the water - just to show that we're not sore losers!!

I'd like to use a cell on my current worksheet (H2) as a reference to a worksheet in another open workbook. I've had a look "out there" and thought that I'd found the solution

Here's what works

=VLOOKUP(E2,'[Cancer PTL reporting (somerset analysis) 2017 07 02.xlsx]Lung'!$H:$I,2,0)

and I thought that what I'd found

=VLOOKUP(A4,INDIRECT("'"C1&"'!A"),2,0)

allows you to keep sheet names like JAN 03.

would lead to

=VLOOKUP(E2,INDIRECT('[Cancer PTL reporting (somerset analysis) 2017 07 02.xlsx]"&H2&"'!$H:$I),2,0)

Ah, this is what it should be

=VLOOKUP(E2,INDIRECT("'[Cancer PTL reporting (somerset analysis) 2017 07 02.xlsx]"&H2&"'!$H:$I"),2,0)

Many thanks,
D€$

 

[SkipVought]

Hi,

Is there a question over there, across the pond?

Your last formula looks correct to me.

However, I'd normalize your data into ONE TABLE for a better data analysis experience.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}