changing formulas
 

I'm working with many formulas like the one below, and would like to know if
there is any way of using a "find" and "replace all" function for a formula.
Such as if I wanted to change all the Kabul's to Chicago for example. Or
will I have to do it manually.


=SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG")*(S4:S 99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG" )*(T4:T99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG" )*(U4:U99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(V4:V99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(W4:W99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(X4:X99="SE 1.1")))

changing formulas
 

ISAF Media Analysis;232429 Wrote:
I'm working with many formulas like the one below, and would like to
know if
there is any way of using a "find" and "replace all" function for a
formula.
Such as if I wanted to change all the Kabul's to Chicago for example.
Or
will I have to do it manually.


=SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG")*(S4:S 99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG" )*(T4:T99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG" )*(U4:U99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(V4:V99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(W4:W99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(X4:X99="SE 1.1")))

You can try Ctrl+H
replace "Kabul" ( no quotes)
with "something_else"

Change the options to look in -Formulas-
Then click -replace all-


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64890

changing formulas
 

Use cell reference instead of hardcode in the formula.

E1: holds Kabul
H1: holds NEG
S1: holds SE 1.1


=SUMPRODUCT((E4:E99=E1)*(H4:H99=H1)*(S4:X99=S1))




"ISAF Media Analysis" wrote:

I'm working with many formulas like the one below, and would like to know if
there is any way of using a "find" and "replace all" function for a formula.
Such as if I wanted to change all the Kabul's to Chicago for example. Or
will I have to do it manually.


=SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG")*(S4:S 99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG" )*(T4:T99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG" )*(U4:U99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(V4:V99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(W4:W99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(X4:X99="SE 1.1")))

changing formulas
 

=SUMPRODUCT(((E4:E99="Kabul")*((H4:H99="NEG")+(J4: J99="neg"))*(S4:X99="SE
1.1")))
& substitute as below
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Teethless mama" wrote in message
...
Use cell reference instead of hardcode in the formula.

E1: holds Kabul
H1: holds NEG
S1: holds SE 1.1


=SUMPRODUCT((E4:E99=E1)*(H4:H99=H1)*(S4:X99=S1))




"ISAF Media Analysis" wrote:

I'm working with many formulas like the one below, and would like to know
if
there is any way of using a "find" and "replace all" function for a
formula.
Such as if I wanted to change all the Kabul's to Chicago for example. Or
will I have to do it manually.


=SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG")*(S4:S 99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG" )*(T4:T99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(H4:H99="NEG" )*(U4:U99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(V4:V99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(W4:W99="SE
1.1")))+SUMPRODUCT(((E4:E99="Kabul")*(J4:J99="NEG" )*(X4:X99="SE 1.1")))