using cells to define sheet references

[patrickgamer]

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

[Elkar]

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