rolling sheet references
 

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?

rolling sheet references
 

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)?

"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.

Can anyone help?

rolling sheet references
 

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/micro...0f6e91b058ac38

(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.

rolling sheet references
 

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!).

"Harlan Grove" wrote:

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/micro...0f6e91b058ac38

(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.

rolling sheet references
 

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

you can copy down

HTH
--
AP

"ScottyC" a écrit dans le message de
...
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?