Copy cell between sheets

  • Thread starter Rev. Michael L. Burns
  • 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
 
G

Gord Dibben

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
 
G

Gord Dibben

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
 
R

Rev. Michael L. Burns

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
 
B

Bassman62

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

Gord Dibben

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
 
B

Bassman62

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top