rolling sheet references

[ScottyC]

I am looking for a way for my formula to reference the same group of cells on
each page, but the sheet name needs to be able to roll when it is pasted (so
I don't have to keep editing it). for example:

sheet1 A1:A7
sheet2 A1:A7
sheet3 A1:A7

and so on.

Can anyone help?

 

[ScottyC]

Oh, I forgot: Is there also a way i can tell the formula to link to a
specific number tab in a workbook (ie, always look to the fifth sheet in the
workbook, no matter what the name of that sheet actually is)--or will the
formula update the sheet reference name when the tabbed display is changed
(for instance 4/1 to 5/1)?

 

[Harlan Grove]

ScottyC wrote...

I am looking for a way for my formula to reference the same group of cells on
each page, but the sheet name needs to be able to roll when it is pasted (so
I don't have to keep editing it). for example:

sheet1 A1:A7
sheet2 A1:A7
sheet3 A1:A7

and so on.

Unfortunately, this is just how Excel works. It's not really a 3D
spreadsheet, so there's no relative worksheet referencing, only
effectively absolute worksheet referencing.

The only workaround is using VBA to write user-defined functions (udfs)
which can provide this functionality. See the following thread in the
Google Groups archive.

http://groups.google.com/group/micr..._frm/thread/9c7516b94f7803a4/da0f6e91b058ac38

(or http://makeashorterlink.com/?N12622EDC ).

I saw your other message, and no, there's no way to specify absolute
positional worksheet references other than using VBA to write other
udfs.

 

[ScottyC]

Thanks for your input, Harlan!

I did figure out how to get Excel to do what I needed it to, It just took a
little time and a lot of Find/Replace (I should have thought of that sooner!).

 

[Ardus Petus]

Assuming your formula is in row 1:
INDIRECT("Sheet"&ROW()&"!A1:A7")

you can copy down

HTH