Excel 97 - automatic page references? 1

[Guest_imported]

I have a formula which refers to a range of cells on another page of the same spreadsheet. I want to be able to copy this formula across a row, and have the page which it refers to change. The names of the pages which I want to refer to are in the row above.

I've discovered the "indirect(address())" function for doing this, but this doesn't seem to work nested in a "lookup" function.

Any ideas?

 

[snyderj]

If you use =INDIRECT(<sheet>&&quot;!<cell>&quot where <sheet> is the cell from which you get your sheet name, and <cell> is the cell reference on the sheet.

If your sheet names do or could include spaces or other non-alphanumeric characters then use this format:
=INDIRECT(&quot;'&quot;&<sheet>&&quot;'!<cell>&quot

This formula will drag and fill a series just fine across a row.

I must give FeJoAl at Askme.com credit for helping me out with this. It was too interesting of a question to not find out how to do it.

 

[Guest_imported]

Many thanks. That works fine being dragged across a row, or nested in LOOKUP functions.

Your advice is much appreciated.

 

[DaleWatson123321]

The &quot;STAR PATROL&quot; has found another instance of a user who apparently is unaware of the need to &quot;show appreciation&quot; by issuing a &quot;STAR&quot;.

Again, for the benefit of &quot;NEW COMERS&quot;, this issuance of a &quot;STAR&quot; to the contributor of your solution, is done by ...simply clicking on the &quot;Let _______ know this post was helpful&quot; - which is located in the lower-left-corner of the contributor's posting.

Because this posting does NOT have anyone listed under &quot;Who's Marked This Thread?&quot;, I will issue &quot;snyderj&quot; a STAR because &quot;Black Sheep&quot; might not see this posting.

...Dale Watson dwatson@bsi.gov.mb.ca

 

[snyderj]

Thank you, Mr. Watson.