If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Worksheet relative reference
Hi,
I have created in a workbook a worksheet containing formulas with relative and absolute references to other worksheets in the same workbook. As I have a large number of workbooks with exactly the same structure (one workbook for each operating company), I tried to copy the worksheet with the formulas to each of the workbooks. However, the formulas in the copied worksheets have absolute references to the initial workbook. Is there any way to specify that the formulas should have relative and not absolute worksheet references? Thnak you, E.g. original formula: =IF(' Scorecard sales orders'!$B62="";... copied formula: =IF('[EBM_O2C_Scorecard_ES.xls] Scorecard sales orders'!$B62="";... |
#2
|
|||
|
|||
Worksheet relative reference
Hi,
No there is not, but you can use the find and replace function to quickly change the reference, press CTRL + H "Ciprian" wrote: Hi, I have created in a workbook a worksheet containing formulas with relative and absolute references to other worksheets in the same workbook. As I have a large number of workbooks with exactly the same structure (one workbook for each operating company), I tried to copy the worksheet with the formulas to each of the workbooks. However, the formulas in the copied worksheets have absolute references to the initial workbook. Is there any way to specify that the formulas should have relative and not absolute worksheet references? Thnak you, E.g. original formula: =IF(' Scorecard sales orders'!$B62="";... copied formula: =IF('[EBM_O2C_Scorecard_ES.xls] Scorecard sales orders'!$B62="";... |
#3
|
|||
|
|||
Worksheet relative reference
I find it easiest to do some editreplace first before copying.
EditReplace What: = With: ^^^ Replace all. Copy the results to other workbook then reverse the editreplace. Close source workbook without saving. Gord Dibben MS Excel MVP On Tue, 27 Apr 2010 08:26:01 -0700, Ciprian wrote: Hi, I have created in a workbook a worksheet containing formulas with relative and absolute references to other worksheets in the same workbook. As I have a large number of workbooks with exactly the same structure (one workbook for each operating company), I tried to copy the worksheet with the formulas to each of the workbooks. However, the formulas in the copied worksheets have absolute references to the initial workbook. Is there any way to specify that the formulas should have relative and not absolute worksheet references? Thnak you, E.g. original formula: =IF(' Scorecard sales orders'!$B62="";... copied formula: =IF('[EBM_O2C_Scorecard_ES.xls] Scorecard sales orders'!$B62="";... |
#4
|
|||
|
|||
Worksheet relative reference
Copy the formulas as normal the Edit Replace [EBM_O2C_Scorecard_ES.xls] with
nothing. -- Regards Dave Hawley www.ozgrid.com "Ciprian" wrote in message ... Hi, I have created in a workbook a worksheet containing formulas with relative and absolute references to other worksheets in the same workbook. As I have a large number of workbooks with exactly the same structure (one workbook for each operating company), I tried to copy the worksheet with the formulas to each of the workbooks. However, the formulas in the copied worksheets have absolute references to the initial workbook. Is there any way to specify that the formulas should have relative and not absolute worksheet references? Thnak you, E.g. original formula: =IF(' Scorecard sales orders'!$B62="";... copied formula: =IF('[EBM_O2C_Scorecard_ES.xls] Scorecard sales orders'!$B62="";... |
Thread Tools | |
Display Modes | |
|
|