Hi,

Try this

=HLOOKUP(A68,(INDEX("'"&indirect(A2&"_table'!A:A") ,(C68*3+2)):INDEX("'"&indirect(A2&"_table'!Q:Q"),4 1)),((13-C68)*3),FALSE))

A2 holds the sheet name. I have not tested it, but it should work

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tkydon" wrote in message
...
This was really helpful.

Is there any way I can use the contents of the cell as part of a worksheet
name,such that a hlookup of another sheet could select a different sheet
based on the content of a cell, instead of having to use a long If
function?

Cell Contents: No.1, No.2, No.3 or No.4

=HLOOKUP(A68,(INDEX('<cell contents_table'!A:A,(C68*3+2)):INDEX('<cell
contents_table'!Q:Q,41)),((13-C68)*3),FALSE))

Where <cell contents is replaced by with the cell contents?