making a portion of reference to a worksheet variable in a formula

[Celia]

How do I make a portion of a worksheet reference variable.

For example I am linking to a file that has multiple worksheets and I am
getting the same data from each sheet. I want to be able to in my formula
link to a column that has the a portion of the worksheet name in it. For
example, column A would list numbers 1 thru 10 and my worksheet name are H1,
H2, H3 .... H10.

Depending on what is in column A determines what worksheet the formula pulls
from.

I am using a two way table lookup so I am using index function & Match like
the following and I want to make the 10 after the H variable (dependent on
what is in column A) . How do I do that



=INDEX('[filename.xls]H10'!$c$1:$g6,MATCH(j5,'[filename.xls]H10'!$c$1:$c$6,0),MATCH(j6,'[filename.xls]H10'!$c$5:$g$5,0))

 

[Jacob Skaria]

Cell A1 = 1 to 10, with cell A1 = 10

Try the below formula
=INDEX(INDIRECT("'[filename.xls]H" & A1 & "'!$c$1:$g6"),
MATCH(j5,INDIRECT("'[filename.xls]H" & A1 & "'!$c$1:$c$6"),0),
MATCH(j6,INDIRECT("'[filename.xls]H" & A1 & "'!$c$5:$g$5"),0))

You can get the same result using VLOOKUP() and MATCH()

=VLOOKUP(J5,INDIRECT("'[filename.xls]H" & A1 & "'!$c$1:$g6"),
MATCH(J6,INDIRECT("'[filename.xls]H"&A1&"'!$c$5:$g$5"),0),0)

If this post helps click Yes