Using cell contents to reference worksheet names



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

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


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




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.


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question