Vary a sheet name in a formula

[bayles]

Hi,

I am trying to reference different sheets in a formula which are named:

CATEGORY HIERARCHY (1)
CATEGORY HIERARCHY (2)
CATEGORY HIERARCHY (3)

The (X) value I have in cell AG537 but for some reason the following formula is not working. I have entered the correct sheet name and the formula works but not when I try to keep ti dynamic.

=HLOOKUP($V537,"'CATEGORY HIERARCHY ("& AG537 &")'!A1:Q35",2,FALSE)

Any ideas?

Thanks

 

[bayles]

Hi,

I figured this one out myself for anyone searching this thread.

=HLOOKUP($V537,INDIRECT("'CATEGORY HIERARCHY ("& VALUE(AG537) &")'!A1:Q35",TRUE),2,FALSE)

Thanks

 

[Jeffrey Mahoney]

You're going to have to use INDIRECT. The text reference it needs is:
=HLOOKUP($V537,Indirect("'CATEGORY HIERARCHY "&AG537&"'!A1:Q35"),2,FALSE)

Jeff