Edit Multiple formulas

[cyclingsam]

I was wondering if there is a way of editing multiple cell references within a range. I.e. adding 1 to every row number in the formula for a selection of cells.

E.g.

I have a spreadsheet with formulas in the range b5:z90 and wish to change every cell reference in these formulas by 1. (from $z$3 to $z$4 for example)

They are all different cell references so I cant simply use the find and replace function.

Thanks in advance for any help

 

[Jon von der Heyden]

Can you do a Find & Replace on $3 with $4, or are there other references that already contain that string?

 

[cyclingsam]

There are multiple different formulas in the different cells so this would still take a lot of time to do with find and replace, the $z$3 to $z$4 was just one example but another cell may have $b$7 and a nother may have $d$2 etc

 

[pgc01]

I guess you'd have to use vba, but you did not post enough information.

1 - Why do you want to change the references?

For ex., is it some sliding window process? In that case you can use names in the formulas. You can use dynamic names that will adjust automatically.

If you explain your problem in more detail maybe there's a more adequate approach to your formulas that makes adjusting the addresses a simple matter.

2 - are the adresses absolute?


As a last resource you'd have to use some code that would change the addresses in the formulas.

 

[cyclingsam]

Hi PGC01

My spreadsheet currently pulls gross values from another spreadsheet. I have been asked to do another spreadsheet for net values.

These net values are in the same spreadsheet as im pulling the gross values from but they are in different rows.

All cell references are absolute.

My guess would be that you would need some sort of code to go into each cell formula and do "current cell reference" + "a number" to get to the desired cell reference but my vba skills are basically non existent so I would not know where to start with this.

Hope this gives a little more info on the problem.

Thanks

 

[pgc01]

Hi

1 -

First a long shot, but that might solve the problem in a simple way:

Can you not ajust the position of the data before copying the formulas?

For ex., let's say that the gross values are 1 row below the net values.

- You insert 1 row before row 1, thus shifting all the data down by 1 row.
- Only then you copy the formulas from the other worksheet. In the other worksheet the formulas refer to the gross values but in this worksheet they will refer to the net values.

This is just the basic idea, that must be adapted to your specific case.


2 -

For the case of the code to change the addresses in the formulas. Can you say that it's simply to go to each formula and add 1 to all row reference, like $a$4:$F$15 becomes $a$5:$F$16?

 

[cyclingsam]

I cant modify the file I am grabbing it from as this feeds other workbooks as well.

2 is exactly what I want to do, but dont know how I could construct this in VBA.

The only other way i thought might be possible to do such a thing would to use the offset function but, again, I dont have much experience with this either.

 

[pgc01]

2 - You just have to loop through the cells with the formulas and look for

"$" + 1-3 letters + "$" + number

and replace the number with number + constant

Although it's not an ironclad solution it should solve your problem.

I'm at work and so I don't have time to do it, but maybe someone else can, or I'll try to do it later.