Change Formula to Text for Manipulation

[Cachilupi]

Using Excel 2010

I have a formula that references another file. I need to make a change to all the formulas, but just the cell reference. For example, if the current formula refers to cell D158 I now want it to be D101, D159 to D102, D160 to D103, etc. The problem comes that after 52 rows it starts over, but refers to a different column (G158 to G101, G159 to G102, G160 to G103, etc). This repeats several time, but the only thing that changes is the row letter.

Is there a way to turn the formula into a text string and then use the "Right" function to extract the row number and then concatenate the string back to make a formula? Right now if use the "Right" function it just returns the value of the formula, I want to be able to manipulate the formula, not the returned value.

Thanks!

 

[ChrisM]

Sure, just highlight all your formula cells, do a find and replace, find "=", replace with "#". Now all your formulas are strings. Edit them, then reverse the find and replace.

 

[Cachilupi]

Chris...that is perfect and so simple. Thank you!

 

[mikerickson]

Alternatly,
you could use a named function

Name: myFunction
RefersTo: =SUM(Sheet1!D10:D100)

and put =myFunction in various cells.
That way any change to the definition of myFunction will be replicated throughout the worksheet