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!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

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.
 

Forum statistics

Threads
1,148,376
Messages
5,746,347
Members
424,009
Latest member
katya12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top