Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace worksheet without affecting the referencing formula
I had a workbook with many worksheets (e.g. WA, WB, WC). Then, in one of the
worksheet, i had formulas that reference to these worksheet (e.g. 'WA'!A1). I would get updates of the worksheets from my colleagues whom i would latter update it to the workbook. What i do is 1. Copy all the updated worksheets to the workbook (which it will named as WA(2), WB(2), WC(2) ). 2. Then, i delete off the original worksheets (except the one with the formula) and rename WA(2) to WA, WB(2) to WB and etc. However, the worksheets with the referencing formula doesn't automatically update itself to the updated worksheet (even though the worksheet name is now the same of the previous worksheet). How do i ensure that the formula is automatically updated to the 'new' worksheet name? Is there any way we could do this? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace worksheet without affecting the referencing formula
Can you just select all the cells on the updated worksheet and copy|paste right
over the existing worksheet? If that doesn't work, then I'd do this: Select your worksheet. Change all the formulas to text select all the cells Edit|replace what: = (equal sign) with: $$$$$= replace all Then replace all the worksheets you need to replace. When you're done, just change all these strings back so that the cells contain formulas: Select all the cells Edit|Replace what: $$$$$= with: = replace all Jay wrote: I had a workbook with many worksheets (e.g. WA, WB, WC). Then, in one of the worksheet, i had formulas that reference to these worksheet (e.g. 'WA'!A1). I would get updates of the worksheets from my colleagues whom i would latter update it to the workbook. What i do is 1. Copy all the updated worksheets to the workbook (which it will named as WA(2), WB(2), WC(2) ). 2. Then, i delete off the original worksheets (except the one with the formula) and rename WA(2) to WA, WB(2) to WB and etc. However, the worksheets with the referencing formula doesn't automatically update itself to the updated worksheet (even though the worksheet name is now the same of the previous worksheet). How do i ensure that the formula is automatically updated to the 'new' worksheet name? Is there any way we could do this? Thanks. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace single 0 without affecting other cells | Excel Worksheet Functions | |||
How can I sort data without affecting formula? | Excel Discussion (Misc queries) | |||
Same formula referencing same cells returns incorrect results, randomly, when pasted into new worksheet | Excel Worksheet Functions | |||
sort worksheet without affecting workbook | Excel Worksheet Functions | |||
Replace worksheet name in formula linked to a different workbook | Excel Discussion (Misc queries) |