Paste Special Multiply

EdE

Board Regular
Joined
Apr 8, 2002
Messages
241
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!
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

ryancarver

New Member
Joined
Sep 12, 2002
Messages
4
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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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

Board Regular
Joined
Apr 8, 2002
Messages
241
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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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

Board Regular
Joined
Apr 8, 2002
Messages
241
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,535
Messages
5,572,759
Members
412,482
Latest member
arooshrana2
Top