using cells to define sheet references
 

I need to dynamically reference sheets in excel and am having a hard
time doing it.

The most eligant solution i have is to use a prefab'd row to define my
sheets.

Here's what I mean:
A1 = "Item 1"
B1 = "Item 2" where "Item 1" and "Item 2" exactly match the names of
other sheets in my file.

I'd like to do something like
A2 = A1!B3
B2 = B1!B3

This way I can simply copy row two accross all the columns to extract
the data from various sheets and include them on a single page.

But I can't find the proper equation to make Excel recognize the
contents of row 1 as the actual sheet names.

Please help, this is driving me mad!!!


--
patrickgamer
------------------------------------------------------------------------
patrickgamer's Profile: http://www.excelforum.com/member.php...o&userid=33918
View this thread: http://www.excelforum.com/showthread...hreadid=536962

using cells to define sheet references
 

The INDIRECT function is what you're looking for. Writing the formlua so
that it will increment appropriately when copied, is a bit more tricky.
Something like this might work for you:

=INDIRECT($A1&"!"&ADDRESS(ROW(B$3),COLUMN(B$3)))

HTH,
Elkar


"patrickgamer" wrote:


I need to dynamically reference sheets in excel and am having a hard
time doing it.

The most eligant solution i have is to use a prefab'd row to define my
sheets.

Here's what I mean:
A1 = "Item 1"
B1 = "Item 2" where "Item 1" and "Item 2" exactly match the names of
other sheets in my file.

I'd like to do something like
A2 = A1!B3
B2 = B1!B3

This way I can simply copy row two accross all the columns to extract
the data from various sheets and include them on a single page.

But I can't find the proper equation to make Excel recognize the
contents of row 1 as the actual sheet names.

Please help, this is driving me mad!!!


--
patrickgamer
------------------------------------------------------------------------
patrickgamer's Profile: http://www.excelforum.com/member.php...o&userid=33918
View this thread: http://www.excelforum.com/showthread...hreadid=536962