using a named range in another worksheet with INDIRECT

[geordz]

evening all,
Hoping some kind person can help me out. I have created a drop down list in 'sheet1' (cell T4) and a variable in A2 that I am performing a VLOOKUP for inanother sheet. The sheet that the VLOOKUP is performed in depends on what is selected in the drop down list ie 'Sheet2', 'sheet3', etc

=VLOOKUP($A$2,INDIRECT("'"&T$4"'!A6:I16"),2,FALSE)

all is working well however, within 'sheet1', 'sheet2' etc I have named the range A6:I16 as 'FP'. I have another range A17:I27 named 'N_1' and a few others. This is so I can create another drop down list in T5 that will have the list 'FP', 'N_1' etc. Is it possible to have both the sheet and range defined dynamically in the INDIRECT function? If so how do I ammend the function above please?

hope that made sense

any help much appreciated

dave

 

[mvptomlinson]

Hi Dave, perhaps:

=VLOOKUP($A$2,INDIRECT("'"&T$4&"'!"&$T$5),2,FALSE)

 

[geordz]

Thank you so much for the reply. That is a great help.

Can you explain the syntax of starting with " ' " and ending with ' "! when you are referencing a sheet? Why is there another set of quotes after the ! ?

The syntax of referencing things using this indirect function is baffling me.

thanks again

dave

 

[mvptomlinson]

Cells T4 and T5 contain variable data, which you want the formula to reference whenever it is calculated. When using INDIRECT, anything within quotation marks will stay the same. Broken down:

"'" will create the first apostrophe before the sheet name.
&T4 will concatenate the value in T4 to that apostrophe.
&"'!" will concatenate an apostrophe and exclamation point after the sheet name.
&T5 will concatenate the value in T5 (named range in this case).

If you kept T4 and T5 within the quotes, it will assume you meant them literally and the formula would become something like:

=VLOOKUP($A$2,'T4'!T5,2,0)

instead of

=VLOOKUP($A$2,'Sheet7'!N_1,2,0)