apply a widespread edit to a formula

[tuffluck]

say i have 10 formulas in 10 different rows. all formulas are different, but i want each one to say something like =if(a1>5,formula,0) but i want each unique formula to be inserted into the "formula" part of my equation. in other words i want to just apply a new formula to a wide array of different formulas and i want that new formula to just be the precursor to the unique formula already in the cell. so just plain ole copy/paste won't do it. for example:

a1 reads =c1*d1
a2 reads =d2/e5*12

and i want them to read:

a1 =if(a10=4,c1*d1,0)
a2 =if(a10=4,d2/e5*12,0)

but i want to be able to write that formula in a1, and then paste the =if() part only into a2. is that possible? otherwise it's a lot of work to change these thousand-something formulas i have in my spreadsheet currently and i would need to hire an admin to do that...just kidding. thanks for the help!

 

[tuffluck]

The only problem with that is there is no ')' to replace in the original example formulas.

the problem is the the false side of the if statement is a slight derivative of the original formula itself, which is different in every single cell. can't replace the ")" with 1 value, it changes every time.

 

[Peter_SSs]

the problem is the the false side of the if statement is a slight derivative of the original formula itself, which is different in every single cell. can't replace the ")" with 1 value, it changes every time.

Well, that may be difficult/impossible with the Find/Replace method but the only two example new/old formulas you gave seemed to have the same 'false side' so that is all anybody had to go on. Also, for the examples you gave, I can't see how the false side is any derivative of the original formula.

Perhaps a more representative sample of before/after formulas and some explanation about how we would decide which 'false' part' had to go with which original formula might help.

 

[Giordano Bruno]

As Peter notes, we would need to know how the false side works. Difficult changes are sometimes made simple by changing over to RICI formula style. Nonetheless, I suspect Weaver is on surer ground here.

 

[tuffluck]

let me try to make it as simple as i can without being ambiguous on the formulas.

let's pretend cell b1 says =a1*Sheet1c6*Sheet2c9

a1 is a series of hlookups that are pulling in various pieces from other worksheets to complete a formula that derives a revenue number. sheet1c6*sheet2c9 correspond to month's worth of expenses. they change every month.

just what i described above happens in over 1,000 lines for 1,000 types of transactions. each item in column a has the same series of lookups to get to a revenue number, but cell b1 and onward is constantly referencing other cells depending on the type of function and month. so while row 1 might be a1*sheet1c6*sheet2c9, row 2 could be a2*sheet19c1*sheet6b5

basically i want to write a formula that says =if(k1=yes,"current formula",a1), but remember on row 2 it would replace the "false" part of the statement to b1, so on and so forth. in other words, the false statement is always referencing a cell that is different for every row. no find/replace would do this unless you did every single row separately, which kind of defeats the point of wanting to find a way to do this cumulatively in 1 step. the reason for the yes/no is contingent upon whether the revenue needs to be constant every month or changing based on the expenses in that given month.

 

[Giordano Bruno]

I'm not sure if you have followed what I said about the RICI reference style.

Written in cells B5 and B6, your first formula "A1*Sheet1!C6*Sheet2!C9" translates to
"R[-4]C[-1]*Sheet1!R[1]C[1]*Sheet2!R[4]C[1]"
and the second "A2*Sheet19!C1*Sheet6!B5" translates to
"R[-4]C[-1]*Sheet19!R[-5]C[1]*Sheet6!R[-1]C"

As you can see, the references to A1 and A2 at the start of the formulas are both expressed as R[-4]C[-1], i.e. 4 rows up and 1 column back. Or they could both be written as R[-4]C1 if, as an example, you want the A to be absolute.

Depending upon the exact nature of the false argument, this approach may or may or may not not be useful. For either the Replace or the VBA option, the exact nature of the False part has to be clearly presented or no one will be able to help you.