change formulas in multiple cells at once

[andrewb90]

Hi all,

I am trying to change the formulas that are in several hundred cells all at once. Every formula's cell reference needs to increase by 140. (i,e, if cell b1 has the formula "=Mon!D20" it should say "=Mon!D160") Is there an easy way to do this with retyping everything?
Not all of the cells are in order so changing the top and filling down won't work for me.

Thanks,

Andrew

 

[John Davis]

Hi all,

I am trying to change the formulas that are in several hundred cells all at once. Every formula's cell reference needs to increase by 140. (i,e, if cell b1 has the formula "=Mon!D20" it should say "=Mon!D160") Is there an easy way to do this with retyping everything?
Not all of the cells are in order so changing the top and filling down won't work for me.

Thanks,

Andrew

If I understand you correctly, select the entire spreadsheet. Then replace D20 with D160.

 

[andrewb90]

That doesn't seem to help. I don't want to change every formula on the sheet.
I did select a group of cells and changed the first formula but it just moved on to the next cell for me to change that formula.

I'm looking to do a fill option that might do what I am looking for.

 

[gregtx81]

Is it correct to say that you want to change every formula that begins with ""=Mon!D" and increase the row by 140?

 

[mikerickson]

Insert 140 cells to the top of column D, the formulas will adjust. (i.e. select D1:D140, press Insert, specify shift down)

You can then use copy/paste to move any data in column D back to its original place.

 

[andrewb90]

but wouldn't that adjust all of the formulas below that? Not every cell on the page needs to be adjusted.

 

[mikerickson]

That method will adjust all of the references to column D in the whole workbook.

References to non-column D cells will be unaffected.

Formulas in column D will be affected, if there are any that need to be moved back, Cut and paste should be used instead of Copy Paste.

 

[andrewb90]

Oh, I see, the cell references are scattered from C:N and they are on 7 different sheets. That would make it a little hard to do.

 

[mikerickson]

How many cells that are not in Column D contain formulas that refer to column D that you want adjusted?

How many cells, that are not in Column D, contain formulas that refer to column D that you do not want adjusted?

 

[clazzic]

I have the same road block when having to duplicate my formulas and then having to manually change a few hundred formulas to reference the correct cells. i used the "find and replace" function after selecting all the cells in which i want to update my formulas. This works wonderfully for me.

for your case. Select all the cells with "=Mon!D20" that you want to change. then Crtl + F. Replace. Then enter the part that you wish to change in "Find what" box. which is D20, then Replace it with the new cell reference "D160"

Good Luck