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

I am performing an HLOOKUP of the info in Col A, and want to refer to
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 my
worksheet, so when I drag my formula to column C, it will reference
cell C1 [DEF], and therefore, worksheet DEF? Directly using cells in
that function does not work [=HLOOKUP(Cell A2,*B1!*...] looks for sheet
B1, which does not exist...

Thank you.

Tim S.


--
timsantiago
------------------------------------------------------------------------
timsantiago's Profile: http://www.excelforum.com/member.php...o&userid=28656
View this thread: http://www.excelforum.com/showthread...hreadid=483242

Hi timsantiago

See the Excel help for the Indirect worksheet function

--
Regards Ron de Bruin
http://www.rondebruin.nl


"timsantiago" wrote in message
...

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

I am performing an HLOOKUP of the info in Col A, and want to refer to
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 my
worksheet, so when I drag my formula to column C, it will reference
cell C1 [DEF], and therefore, worksheet DEF? Directly using cells in
that function does not work [=HLOOKUP(Cell A2,*B1!*...] looks for sheet
B1, which does not exist...

Thank you.

Tim S.


--
timsantiago
------------------------------------------------------------------------
timsantiago's Profile: http://www.excelforum.com/member.php...o&userid=28656
View this thread: http://www.excelforum.com/showthread...hreadid=483242

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" wrote:


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

I am performing an HLOOKUP of the info in Col A, and want to refer to
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 my
worksheet, so when I drag my formula to column C, it will reference
cell C1 [DEF], and therefore, worksheet DEF? Directly using cells in
that function does not work [=HLOOKUP(Cell A2,*B1!*...] looks for sheet
B1, which does not exist...

Thank you.

Tim S.


--
timsantiago
------------------------------------------------------------------------
timsantiago's Profile: http://www.excelforum.com/member.php...o&userid=28656
View this thread: http://www.excelforum.com/showthread...hreadid=483242

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.


--
timsantiago
------------------------------------------------------------------------
timsantiago's Profile: http://www.excelforum.com/member.php...o&userid=28656
View this thread: http://www.excelforum.com/showthread...hreadid=483242

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


"timsantiago" wrote:


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.


--
timsantiago
------------------------------------------------------------------------
timsantiago's Profile: http://www.excelforum.com/member.php...o&userid=28656
View this thread: http://www.excelforum.com/showthread...hreadid=483242

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

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

Thanks for your help guys!


--
timsantiago
------------------------------------------------------------------------
timsantiago's Profile: http://www.excelforum.com/member.php...o&userid=28656
View this thread: http://www.excelforum.com/showthread...hreadid=483242