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 |
Copy cell between sheets
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 cell between sheets
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 |
Copy cell between sheets
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 |
Copy cell between sheets
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 cell between sheets
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 |
Copy cell between sheets
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 |
All times are GMT +1. The time now is 11:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com