Copy cell between sheets

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

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

Just an added note.

Your Excel life will be much easier if you learn proper cell referencing
syntax.

Column 5, row 3 is E3 if using A1 notation......columns have letters, rows
have numbers

If using R1C1 notation(columns and rows both have numbers) row 3, column 5
would be R3C5

The vast majority of users deal in A1 notation.


Gord


On Wed, 18 Mar 2009 15:25:25 -0700, Gord Dibben gorddibbATshawDOTca wrote:

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

Thanks. That's what I wanted.

Michael


"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

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

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

Thank you. Works like a charm!


"Gord Dibben" gorddibbATshawDOTca wrote in message
...
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