Formula Range Changes

[gymwrecker]

Every time that I clear contents to copy and paste new data in the "Master" worksheet, the formula range where the formula is changes. A2:A1000 is what I need to keep. Please advice.

=SUMPRODUCT(ISNUMBER(SEARCH("*0084*",Master!$A$2:$A$1000))*ISNUMBER(SEARCH("*OD*",Master!$K$2:$K$1000))*ISNUMBER(SEARCH("*1A.*",Master!$U$2:$U$1000)))

 

[MrKowz]

Try:

=SUMPRODUCT(ISNUMBER(SEARCH("*0084*",INDIRECT("Master!$A$2:$A$1000")))*ISNUMBER(SEARCH("*OD*",Master!$K$2:$K$1000))*ISNUMBER(SEARCH("*1A.*",Master!$U$2:$U$1000)))

 

[gymwrecker]

Thanks MrKowz!

It did not work...no results were returned. Please advice.

=SUMPRODUCT(ISNUMBER(SEARCH("*0084*",INDIRECT("Master!$A$2:$A$1000"))*ISNUMBER(SEARCH("*OD*",Master!$K$2:$K$1000))*ISNUMBER(SEARCH("*1A.*",Master!$U$2:$U$1000))))

 

[MrKowz]

You're missing a parenthases:

=SUMPRODUCT(ISNUMBER(SEARCH("*0084*",INDIRECT("Master!$A$2:$A$1000")))*ISNUMBER(SEARCH("*OD*",Master!$K$2:$K$1000))*ISNUMBER(SEARCH("*1A.*",Master!$U$2:$U$1000)))

 

[gymwrecker]

Ooops! It's working now...Thanks again...!!!!

Now, is there a way to fix ALL my formulas at once? I have 128 cells that I have to fix..

 

[MrKowz]

Try selecting the range of cells that have the formulas, and doing a Find/Replace:

Find - Master!$A$2:$A$1000
Replace - INDIRECT("Master!$A$2:$A$1000")

Note: Don't include the formula you already did this on, otherwise you'll double-up the INDIRECT formula.