Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel 2003 - Copy Cell Reference
I have a cell reference in my last worksheet of my workbook e.g. =Sheet1!B5
All the other worksheets are set up in the same way and so I am trying to copy this refence so that I would get =Sheet2!B5 =Sheet3!B5 etc. so that it saves me time not having to change all the cells manually. My guess is, this is not possible as worksheets are absolute and not relative?! Or is there any smart way how to get around this problem? Kind regards Nicole |
#2
|
|||
|
|||
Nicole
One method..... If sheets are named Sheet1, Sheet2 etc. enter this formula in A1 of your master sheet. =INDIRECT("'Sheet" & (ROW() & "'!$B$5") Drag/copy down column A. If sheets have unique names, enter the sheet names in a column and use this formula. =INDIRECT(X1 & "!$B$5") If spaces in sheetnames use =INDIRECT("'" & X1 & "'!$B$5") Assume sheet names were in X1:X10 you would copy down 10 cells. Your ranges may differ, so adjust to suit. Gord Dibben Excel MVP On Tue, 6 Sep 2005 17:09:20 -0700, Nicole wrote: I have a cell reference in my last worksheet of my workbook e.g. =Sheet1!B5 All the other worksheets are set up in the same way and so I am trying to copy this refence so that I would get =Sheet2!B5 =Sheet3!B5 etc. so that it saves me time not having to change all the cells manually. My guess is, this is not possible as worksheets are absolute and not relative?! Or is there any smart way how to get around this problem? Kind regards Nicole |
#3
|
|||
|
|||
Cool!!! You are the best! :)
Many thanks! Nicole "Gord Dibben" wrote: Nicole One method..... If sheets are named Sheet1, Sheet2 etc. enter this formula in A1 of your master sheet. =INDIRECT("'Sheet" & (ROW() & "'!$B$5") Drag/copy down column A. If sheets have unique names, enter the sheet names in a column and use this formula. =INDIRECT(X1 & "!$B$5") If spaces in sheetnames use =INDIRECT("'" & X1 & "'!$B$5") Assume sheet names were in X1:X10 you would copy down 10 cells. Your ranges may differ, so adjust to suit. Gord Dibben Excel MVP On Tue, 6 Sep 2005 17:09:20 -0700, Nicole wrote: I have a cell reference in my last worksheet of my workbook e.g. =Sheet1!B5 All the other worksheets are set up in the same way and so I am trying to copy this refence so that I would get =Sheet2!B5 =Sheet3!B5 etc. so that it saves me time not having to change all the cells manually. My guess is, this is not possible as worksheets are absolute and not relative?! Or is there any smart way how to get around this problem? Kind regards Nicole |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do you copy the same cell reference on different worksheets | Excel Worksheet Functions | |||
How do I get top cell alignment in excel 2003 | Excel Discussion (Misc queries) | |||
Enter an Excel cell reference as part of a custom header/footer | Excel Worksheet Functions | |||
Can't Copy and Paste between Excel 2003 Workbooks | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |