Using INDIRECT with a concatenated reference

[GreenyMcDuff]

Hi all,

I am using this formula to look up values from another worksheet:

=VLOOKUP($B9,INDIRECT("'"&$B10&"'!$A:$W"),D$4,0)

This formula works fine.

However, I now need the sheet reference to be B10 but with " - Table (1" at the end of it.

So assuming cell b10 had the word Apples in it the vlookup would read:

=VLOOKUP($B9,'Apples - Table (1'!$A:$W"),D$4,0)

I have tried to concatenate this in the formula but have got nowhere fast.

Hopefully someone can help me

Thanks

Chris

 

[Jose_Chasez]

This works for me. Just be careful that the string you're creating below exactly matches your worksheet name (i.e., spaces around the dash, etc.)

=VLOOKUP($B9,INDIRECT("'"&$B10&" - Table (1'!$A:$W"),D$4,0)

 

[GreenyMcDuff]

Thanks worked a treat, I had way too many &'s in mine