Paste Special Multiply

[EdE]

How do I use paste special/multiply so that when I copy a cell then multiply, it uses the cell reference instead of the number in that cell. I.E. I have in A5, =a3, and in a1 have 25, but I want in a5, =a3/a1. I ask because I have several thousand formulas to modify to add/divide/multiply by only 3 different cells. Or is there an easier way to do this?

Thanks!

 

[ryancarver]

could you be a little more clear. Having a hard time understanding what you actually need to do - I do get the prospect of changing a zillion cells - not fun

 

[Juan Pablo González]

I don't think that Paste Special (divide, not multiply in your case) can do what you want... you would have to use a macro to change the cells you specify.

 

[EdE]

I have rows of formulas that currently do various calculations. Now some of these formulas must make the same calculation and divided by a value that can be changed. I.E.
Now-->a4+a5 Needs to be (a4+a5)/a1.
Does this make sense? Or is a massive find/replace in order?
Thanks?

 

[Juan Pablo González]

Yes, look at my post... Find / Replace shouldn't work because, unless your formulas were constructed with a pair of ( ) at the beginning and end of each formula, you would screw them up ! because this

=A4+A5

would be converted as

=A4+A5/A1

instead of the correct

=(A4+A5)/A1

 

[IML]

If you follow these steps this should work.
Put an odd number in a1 (like 2.11111)
Use find (in formula) to search for any other instances of 2.11111 (if there are any choose a different number).
Copy a1, and paste special divide over your range.
Now use find and replace over the same range and find 2.11111 and replace with A1.

 

[EdE]

Thanks. I thought I could use the paste special function. What I did to resolve was highlighted the entire section I needed to change, F2, made my changes, then ctrl+enter. Worked pretty good. About as efficient I guess.