Referencing another cell in a function

[Matt]

I am trying to reference another cell in an INDEX function
but cannot seem to make it work. The function I am using
is:
=INDEX('?????'!B:J,MATCH(A2,'?????'!B:B,0),8)

The ?'s in the function refer to a value(another worksheet
name) in the cell above the function that I would like to
substitute.

Any help would be appreciated.

Thanks

 

[Chip Pearson]

Matt,

You can use the INDIRECT function to do this. Assuming that the
sheet name is in A1, use something like

=INDEX(INDIRECT("'"&A1&"'!BJ"),MATCH(A2,INDIRECT("'"&A1&"'!B:B"),
0),8)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com