Find and Replace

Harold R.

Board Regular
Joined
Sep 26, 2004
Messages
132
I have a wrong Formula in a workbook that contain 18 worksheets. The wrong formula is:
=SUM((D12<C12)+D12-C12+(F12<E12)+F12-E12+(I12<H12)+I12-H12)*24
Is there a easy way of using find and replace this formula in each worksheet with :=SUM((D12<C12)+D12-C12+(G12<E12)+G12-E12+(I12<H12)+I12-H12)*24
Of coure the row number will change because the formula is in at least 30 rows on each sheet?
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Harold R.

Board Regular
Joined
Sep 26, 2004
Messages
132
A formula that has the wrong row refference Sum A11<B11 should be SUM B11< C11
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870

ADVERTISEMENT

Hi Harold

When posting formulas be careful with the "< " sign because HTML tries to interpret it and makes a mess, like in your post. In that case use the code button in the post reply form before and after the formula.

I believe your formula is

Code:
=SUM((D12<C12)+D12-C12+(F12<E12)+F12-E12+(I12<H12)+I12-H12)*24

and you want to replace it with

Code:
=SUM((D12<C12)+D12-C12+(G12<E12)+G12-E12+(I12<H12)+I12-H12)*24

The problem here is that "the formula is in at least 30 rows on each sheet". This means different columns, different rows, etc.

In this case I suggest you work in R1C1 notation. Since your formula has only relative addresses it does not change when you move it around.
You can then select the worksheets and use a Find/Replace.

Hope this helps
PGC
 

Harold R.

Board Regular
Joined
Sep 26, 2004
Messages
132
What is R!C! Notation and how do I do it? I know I am showing my stupidity but I don't know. Thanks
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
Hi Harold

There's a lot to learn. We all know very little.

You can use in excel 2 reference styles: A1 and R1C1.

In A1 reference style (the one you are using in the formula) you refer to a cell using letters to indicate the column and numbers to indicate the row.

In R1C1 you use numbers for both the column and the row.

Your formula only uses relative addresses. In this case a formula using R1C1 notation does not change when copied.

Example.

You have in B3
=A1+3
You copy it to B4 and you have
=A2+3
Now copy the formula to C4, you have
=B2+3

Although all 3 formulas mean the same: "cell 2 rows up and one column left +3", due to the A1 notation it is always changing, both the row and the column.

Now in R1C1 reference style, if in cell R3C2 (B3) you write the equivalent to (=A1+3)
=R[-2]C[-1]+3
In this notation if you copy it down or right it will not change because it does not depend on the cell that you enter it. It always means: "cell 2 rows up and one column left +3"

So what I meant was: turn R1C1 reference style on, in Tools>Options>General and then, since the formula is always the same you can select the worksheets and use Find/Replace.

You just need to use the command once if you select all the sheets where the formula is.

I hope it was clear.
PGC
 

Harold R.

Board Regular
Joined
Sep 26, 2004
Messages
132
Yes that worked great thanks. I continue to be amazed by what you can do with Excel. Again thank you very much for your help. :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,112,211
Messages
5,541,845
Members
410,550
Latest member
ganeshsamant
Top