Change each formula by 1

[subtilty]

Hi Guys,

Is it possible to have a list of formulae and have the cell reference change by 1 without having to go into each one and change it manually?

Example:

You have;
=a1
=a3
=a5
=a7

And change them all to;
=a2
=a4
=a6
=a8

So the reference cell changes by one digit.

Thanks in advance!

[romperstomper]

Is this an existing list of formulas? If so, then no, you will need to alter each one, though you can do that fairly quickly if the pattern is regular. If not, then you could use a different formula that allows you to specify the start cell and then have each subsequent formula offset by two rows from that.

[subtilty]

Hi, Thanks for the quick reply.

Yes they existing formulas. And I need each formula to change the cell reference by 1 to indicate the cell below it. There are over 100 formulae to change to the new numbers for about 10 people so was hoping there was a quicker way of performing the change.

[romperstomper]

Select the formulas, choose Edit-Replace, and enter:
Find: =
Replace: XX
and press Replace all.
Then alter the first two cells to XXA2 and XXA4, select those two cells and copy down using the fill handle. Now select the cells again and do a Find-Replace but this time use:
Find: XX
Replace: =
and you should be set.

[subtilty]

Thanks! I guess thats a good workaround.