Hi all,

I am looking for a way to do dynamic substitution of the worksheet
reference in a formula. For example, let's say I have 50 worksheets of
data (names 'DATA1' through 'DATA50'. Each worksheet is identical in
layout, but with different data. Now I want a summary sheet (named
'SUMMARY') that extracts certain data from each worksheet and each
column on the summary is for a different data set.

What I want is to have a string with a page name in one cell,
and below that formula that get the page name string from above and
substitute that into a worksheet/cell reference.

Example:

Cell 'SUMMARY!A1' contains 'DATA3'
Cell 'SUMMARY!A2' contains '=a1!$d$10' (without the quotes, of course)

Now the formula should sustitute the string DATA3 for the a1 reference
and we should end up with whatever data is stored in DATA3!D10.

Now I should be able to copy the formula in SUMMARY!A2 to the right,
and put any valid page name in the cell above to return the D10 value
for that data set.

Any ideas?

Kind regards,

Bram Weisman



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Cell 'SUMMARY!A2' should contain =INDIRECT(a1&"!$d$10")

--
David Hager
Excel MVP


"bramweisman" wrote in message
...
Hi all,

I am looking for a way to do dynamic substitution of the worksheet
reference in a formula. For example, let's say I have 50 worksheets of
data (names 'DATA1' through 'DATA50'. Each worksheet is identical in
layout, but with different data. Now I want a summary sheet (named
'SUMMARY') that extracts certain data from each worksheet and each
column on the summary is for a different data set.

What I want is to have a string with a page name in one cell,
and below that formula that get the page name string from above and
substitute that into a worksheet/cell reference.

Example:

Cell 'SUMMARY!A1' contains 'DATA3'
Cell 'SUMMARY!A2' contains '=a1!$d$10' (without the quotes, of course)

Now the formula should sustitute the string DATA3 for the a1 reference
and we should end up with whatever data is stored in DATA3!D10.

Now I should be able to copy the formula in SUMMARY!A2 to the right,
and put any valid page name in the cell above to return the D10 value
for that data set.

Any ideas?

Kind regards,

Bram Weisman



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Hi Bram,

=INDIRECT(A1&"!D10")

HTH
Anders Silvén

"bramweisman" skrev i meddelandet
...
Hi all,

I am looking for a way to do dynamic substitution of the worksheet
reference in a formula. For example, let's say I have 50 worksheets of
data (names 'DATA1' through 'DATA50'. Each worksheet is identical in
layout, but with different data. Now I want a summary sheet (named
'SUMMARY') that extracts certain data from each worksheet and each
column on the summary is for a different data set.

What I want is to have a string with a page name in one cell,
and below that formula that get the page name string from above and
substitute that into a worksheet/cell reference.

Example:

Cell 'SUMMARY!A1' contains 'DATA3'
Cell 'SUMMARY!A2' contains '=a1!$d$10' (without the quotes, of course)

Now the formula should sustitute the string DATA3 for the a1 reference
and we should end up with whatever data is stored in DATA3!D10.

Now I should be able to copy the formula in SUMMARY!A2 to the right,
and put any valid page name in the cell above to return the D10 value
for that data set.

Any ideas?

Kind regards,

Bram Weisman



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Thanks guys! INDIRECT is exactly what I needed.

Best wishes,

Bram Weisman



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/