Copy cell between sheets

  • Thread starter Thread starter Rev. Michael L. Burns
  • Start date Start date
R

Rev. Michael L. Burns

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
 
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
 
Thanks. That's what I wanted.

Michael


Gord Dibben said:
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
Click to expand...
 
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.
 
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
 
Thank you. Works like a charm!


Gord Dibben said:
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
Click to expand...
 
Back
Top