'Replace' Query

[Mathew P Bennett]

Evening All,

I have, what I hope is an aesy question to answer, regarding the 'replace' function.
I have the following formula, (simplified from actual),
=SUMIF($H$6:$H$84,$AA$4,I$6:I$84)+($AC$2*$AB$4/2)

But I would like to add the following IF function at the start, =IF($X9=0,0,
so that the whole thing should read:

=IF($X9=0,0,(SUMIF($H$6:$H$84,$AA$4,I$6:I$84)+($AC$2*$AB$4/2))

Firstly, I have tried replacing =SUMIF( with =IF($X9=0,0,SUMIF(
but it does not allow this as there is no second closing parenthesis.

Also, if this is possble, I would also like it to be able to automatically alter the $X9 to $X10 etc
as the formula is replaced as I go down the sheet, ( ie by row number, where the 9,10 etc is the row number.

As usaual any help on this troublesome problem would be appreciated.

Cheers,
Mathew

 

[Jim Rech]

I may not be understanding you but it seems you're trying to use a formula
to alter other formulas (with the Replace worksheet function). That's not
going to work. The Replace function operates on the "value" of a cell, not
its "contents". With a string/text entry they are the same thing but with a
formula they are obviously different. Replace can only operate on whatever
the formula evaluates to, which is usually a number.

If you want to actually change formulas in cells you have to do it manually
or with a macro.