Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I repeatedly substitute a value in a formula
I have a spreadsheet with date references repeatedly used in the formula. I
wish to change the formula from 07 to 08 and not have to manually change in every cell it appears |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I repeatedly substitute a value in a formula
Maybe you could post a sample or two of your "hardcoded" formula for a
better answer? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "rob_t" wrote in message ... I have a spreadsheet with date references repeatedly used in the formula. I wish to change the formula from 07 to 08 and not have to manually change in every cell it appears |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I repeatedly substitute a value in a formula
thanks max eg following='C:\Users\Robert\Documents\Robert\Budget \[Visa
2007.xls]Jan 07'!C51 I need to update to 2008 "Max" wrote: Maybe you could post a sample or two of your "hardcoded" formula for a better answer? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "rob_t" wrote in message ... I have a spreadsheet with date references repeatedly used in the formula. I wish to change the formula from 07 to 08 and not have to manually change in every cell it appears |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I repeatedly substitute a value in a formula
I would select the range to fix and then
edit|Replace what: 2007 with: 2008 replace all and what: 07 with: 08 replace all If there was a chance that the 2007 showed up in other places in the formula, I'd try to give the edit|replace more info: Edit|replace what: 2007.xls]jan 07 with: 2008.xls]jan 08 replace all rob_t wrote: thanks max eg following='C:\Users\Robert\Documents\Robert\Budget \[Visa 2007.xls]Jan 07'!C51 I need to update to 2008 "Max" wrote: Maybe you could post a sample or two of your "hardcoded" formula for a better answer? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "rob_t" wrote in message ... I have a spreadsheet with date references repeatedly used in the formula. I wish to change the formula from 07 to 08 and not have to manually change in every cell it appears -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I repeatedly substitute a value in a formula
"rob_t" wrote:
eg following='C:\Users\Robert\Documents\Robert\Budget \[Visa 2007.xls]Jan 07'!C51 I need to update to 2008 Think you could try using Edit Replace in this sequence (tested ok here) Suppose you want to change/update the link formula: ='C:\Users\Robert\Documents\Robert\Budget\[Visa 2007.xls]Jan 07'!C51 to: ='C:\Users\Robert\Documents\Robert\Budget\[Visa 2008.xls]Jan 08'!C51 ie both filename and sheetname (2008, 08) needs to be updated (It's assumed you already have the new file: Visa 2008.xls with the sheet/s: Jan 08, Feb 08, etc in the same path) Select all the link formula ranges Click Edit Replace Find what: = Replace with: xxx Click "Replace All" Click OK to dismiss the prompt (This converts all the formulas to text. The "xxx" is just an arbitrary, "unique" text) Then with the Find and Replace dialog still there/same formula ranges selected just change the settings in the dialog to: Find what: 2007 Replace with: 2008 Click "Replace All" Click OK to dismiss the prompt (This changes all the filenames from 2007 to 2008) Then change the settings in the Find and Replace dialog to: Find what: <space07 (enter a space before "07") Replace with: <space08 (enter a space before "08") Click "Replace All" Click OK to dismiss the prompt (This changes all the sheetnames from 07 to 08) Finally, restore all the "=" signs, ie change the settings in the Find and Replace dialog to: Find what: xxx Replace with: = Click "Replace All" Click OK to dismiss the prompt, Close the dialog That should do it -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUBSTITUTE formula -- variable spacing between parameters?? | Excel Worksheet Functions | |||
Concatonate and Substitute show the formula rather than the result | Excel Worksheet Functions | |||
Nesting the SUBSTITUTE formula | Excel Worksheet Functions | |||
Substitute Formula | Excel Worksheet Functions | |||
Excel Crashes Repeatedly | Excel Discussion (Misc queries) |