dynamic substitution of worksheet reference

  • Thread starter Thread starter bramweisman
  • Start date Start date
B

bramweisman

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