Copy/paste this UDF to a general module in your workbook.

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

Example of usage...................

Say you have 12 sheets, sheet1 through sheet12...........sheet names don't
matter.

In sheet1 you have a value of 100 in A1

Select second sheet and SHIFT + Click last sheet

In active sheet A1 enter =PrevSheet(A1) + 1

Ungroup the sheets.

Each A1 of sheets past sheet1 will increment the 100 to 101, 102 etc.


Gord

On Thu, 19 Mar 2009 16:26:05 -0500, "Bassman62"
wrote:

Gord,

When I saw this thread I thought it may be addressing something like copying
a formula across sheets and have the formula flow as relative such such as:
Sheet2!A1=Sheet1!A1+1, Sheet3!A1=Sheet2!A1+1...
I enjoyed this feature in Lotus because it was easy to create sheet to sheet
references.
I know that Lotus is actually 3D and Excel is not but was wondering if any
enhancements have been added recently to work around this deficiency?
Thanks.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
You want to have the cells to be linked to Sheet1 or just be a one time
copy?

1. To link them

CTRL + Click on sheet tabs to select sheets 2 through 5

In E3 of active sheet enter =Sheet1!E3

Ungroup the sheets by selecting Sheet1

Whatever you enter in E5 will be transferred to the other sheets.

2. One time copy.

Select Sheets 1 through 5 then in E3 of Sheet1 enter something.

This will be entered on all sheets, but not be linked.


Gord Dibben MS Excel MVP


On Wed, 18 Mar 2009 16:48:25 -0500, "Rev. Michael L. Burns"
wrote:

Several years ago I had a spreadsheet (I think it was Lotus) where I had a
cell on one sheet that was automatically reflected on several other
sheets.
For the life of me I cannot figure out how I did it in Lotus or how to do
it
here in Excel 2007. I'm sure it's really simple and that I'm just a little
too dense. Can anyone point me in the right direction?

For instance, I might want the contents of sheet 1 row 3, column 5 to be
reflected on sheets 2, through 5 at row 3 column 5. How can I do this?

Thanks,
Michael