Using cell contents to reference worksheet names

[timsantiago]

I have a Workbook with about 20 worksheets (say, 'ABC', 'DEF', etc).
also have a summary sheet within this workbook. So, *B1='ABC'*
C1='DEF', etc. Column A is a list of all the information in m
worksheets. The rest is a tally of quantities of each row in eac
worksheet.

I am performing an HLOOKUP of the info in Col A, and want to refer t
the worksheets in row 1 [=HLOOKUP(Cell A2,*ABC!*...]. My question is
How can I use cell B1 (ABC), as part of my formula to reference m
worksheet, so when I drag my formula to column C, it will referenc
cell C1 [DEF], and therefore, worksheet DEF? Directly using cells i
that function does not work [=HLOOKUP(Cell A2,*B1!*...] looks for shee
B1, which does not exist...

Thank you.

Tim S

 

[Ron de Bruin]

Hi timsantiago

See the Excel help for the Indirect worksheet function

 

[Guest]

I'm not really clear how you are trying to go about this, but you need to use
the INDIRECT() function.

If the text value "ABC" is in cell A1, and you want to reference cell C10 on
the tab named "ABC", you'd use

=INDIRECT(A1&"!C10)

 

[timsantiago]

Ron,

Thanks for your reply. I have tried to implement this into my formula,
but it's not working. I'm having a difficult time understanding how the
INDIRECT worksheet function works.

I reentered the formula as HLOOKUP(Cell
A2,INDIRECT(B$1),data_range,#,FALSE)... but how do you implement the
exclamation point used for worksheet referencing? Also, INDIRECT
doesn't seem to reference the desired worksheet, as my data range is
being highlighted within the summary sheet.

 

[Guest]

=HLOOKUP(Cell A2,INDIRECT(B$1&"!data_range",#,FALSE)

 

[timsantiago]

Since the requirements for INDIRECT are =INDIRECT(Ref_text,A1), th
",#,FALSE" didn't fit. The formula was modified as follows:

=HLOOKUP(Cell A2,INDIRECT(B$1&"!data_range"),#,FALSE)

Thanks for your help guys