Row 1 contains labels / headings:

A B C
Sheet 1 Sheet 2 Sheet 3, etc.

In row 2, part of the formula will look to a cell on the worksheet that
corresponds to the heading. For example, "='Sheet 1'!A3+'Sheet 1'!A5"

I'd like to "insert" the reference to the worksheet by referencing the cell
in row 1. I think there must be an easy way to do it, and it would save me a
lot of time.

Thanks in advance!

=INDIRECT(SUBSTITUTE(A1," ",)&"!A3")+INDIRECT(SUBSTITUTE(A1," ",)&"!A5")


"JZinGA" wrote:

>
> Row 1 contains labels / headings:
>
> A B C
> Sheet 1 Sheet 2 Sheet 3, etc.
>
> In row 2, part of the formula will look to a cell on the worksheet that
> corresponds to the heading. For example, "='Sheet 1'!A3+'Sheet 1'!A5"
>
> I'd like to "insert" the reference to the worksheet by referencing the cell
> in row 1. I think there must be an easy way to do it, and it would save me a
> lot of time.
>
> Thanks in advance!

Here's the formula for B1:
=INDIRECT("'"&A$1&"'!$A$3")+INDIRECT("'"&A$1&"'!$A$5")
Here's another hint:
Somewhere on each sheet (say in cell A10) place this formula to return the
Sheet Name.
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
Then, on your lookup sheet, on row one, use a reference formula to point to
A10 on each sheet.
For example A1 would be ='Sheet1'!$A$10
This way, if a sheet name changes all the formulae will update
automatically.

Hope this helps.
--
David K.



"JZinGA" <(E-Mail Removed)> wrote in message
news:30996989-2D12-494D-AF4A-(E-Mail Removed)...
>
> Row 1 contains labels / headings:
>
> A B C
> Sheet 1 Sheet 2 Sheet 3, etc.
>
> In row 2, part of the formula will look to a cell on the worksheet that
> corresponds to the heading. For example, "='Sheet 1'!A3+'Sheet 1'!A5"
>
> I'd like to "insert" the reference to the worksheet by referencing the
> cell
> in row 1. I think there must be an easy way to do it, and it would save
> me a
> lot of time.
>
> Thanks in advance!