Using cell contents to reference worksheet names

[timsantiago]

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.

[Ron de Bruin]

Hi timsantiago

See the Excel help for the Indirect worksheet function

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


"timsantiago" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Duke Carey]

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
>
>

[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.

[Duke Carey]

=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
>
>

[timsantiago]

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!