how do I repeatedly substitute a value in a formula

  • Thread starter Thread starter rob_t
  • Start date Start date
R

rob_t

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
 
thanks max eg following='C:\Users\Robert\Documents\Robert\Budget\[Visa
2007.xls]Jan 07'!C51 I need to update to 2008
 
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 said:
thanks max eg following='C:\Users\Robert\Documents\Robert\Budget\[Visa
2007.xls]Jan 07'!C51 I need to update to 2008

Max said:
Maybe you could post a sample or two of your "hardcoded" formula for a
better answer?
Click to expand...
Click to expand...
 
rob_t said:
eg following='C:\Users\Robert\Documents\Robert\Budget\[Visa
2007.xls]Jan 07'!C51
I need to update to 2008
Click to expand...

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: <space>07 (enter a space before "07")
Replace with: <space>08 (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

---
 
Back
Top