Results 1 to 15 of 20

20080206, 18:09 #1
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Paste special to create a formula (All versions)
Hi,
Can I just bounce a question off the Excel experts here.
I was showing someone how to use paste special to update values, by coping a value like 1.20 and pasting it over a selection of values using the paste special : multiply option. they then told me that they saw someone do something similar, but the resulting paste special created a formula in each of the cells that were pasted. I was experimenting with this and cannot seem to get it to work. I initially thought it was a paste link option until they demoed me something like this:
The type =10 into a cell and copy it. Then they select the values and paste special : multiply. It then multiplies 10 into the number and creates a formula without cell references. This is close to what they were after, but they wanted the 10 to be represented as a cell reference, so if they change 10 in the cell, it updates all the cells that were pasted over.
Any ideas if this is possible?
Hope its clear!Regards,
Rudi

20080206, 18:20 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Paste special to create a formula (All versions)
The question bounced right off me, so you're none the wiser... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

20080206, 18:24 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Paste special to create a formula (All versions)
But seriously, I don't think that can be done, though I'll be happy if someone corrects me.
I gather from discussions elsewhere that Excel 2007 doesn't even perform the trick you describe. I can't test that, though.

20080206, 18:30 #4
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Paste special to create a formula (All versions)
Maybe not! I think I made a little breakthrough!
This theory works if there is a formula in the cell. For instance:
A1 has the value 10
A2 has the value 20
A3 has the value =A1+A2
C1 has the value =2
Now I select cell C1 and copy
Select cell A3 and paste special : multiply
The result in A3 is =(A1+A2)*2
I am just needing to find a way to have it paste the reference C1 in place of the 2. Maybe using find/replace? or INDIRECT?
As I say, its just and experiment to verify if this is possible, based on a claim from another person!Regards,
Rudi

20080206, 18:40 #5
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Paste special to create a formula (All versions)
It seems you're right. It cannot be done directly in the cell. I have managed to get it right by "cheating".
Using the method I described above, I was trying to copy '=C1, but it does not paste at all! (Text does not work!)
Then I used an arbitrary number like =999. Copied and pasted over other values. This created the formula =(A1+A2)*999. Then use Find/Replace to replace 999 with C1. This created the desired formula in all the pasted cells. But I guess this is a quick fix.
Final resolution...
It seems your serious answer was the bounce I required. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>Regards,
Rudi

20080206, 18:47 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Paste special to create a formula (All versions)
Not sure if I understand.
Assume you have values and/or formulas in A1:A3
In C1 you enter the number 2
In D1 enter the formula:
=$C$1
Then copy D1
select A1:A3
pastespecial multiply
And it will create formulas in A1:A3 (even if they had numbers) multiplied by $C$1 [Note the locked references are important unless you want to copy relatively...]
Steve

20080206, 19:00 #7
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Paste special to create a formula (All versions)
Hi Steve,
Using the method you specify does not create a formula in A1:A3. It simply increases the value in the cell by multiplying by 2. I wanted the result to be =[value in A1]*$C$1. I don't think Paste special can paste a cell reference into a cell.Regards,
Rudi

20080206, 19:08 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Paste special to create a formula (All versions)
It works for me in Xl2002....
After starting with what you mentioned in <post#=693,447>post 693,447</post#>
A1 has the value 10
A2 has the value 20
A3 has the value =A1+A2
C1 has the value =2
and doing what I detailed I get in A1:A3
<table border=1><td></td><td align=center>A</td><td align=center valign=bottom>1</td><td valign=bottom>=10*($C$1)</td><td align=center valign=bottom>2</td><td valign=bottom>=20*($C$1)</td><td align=center valign=bottom>3</td><td valign=bottom>=(A1+A2)*($C$1)</td></table>
Is this not what you are after? The key is to copy D1 (which contains =$C$1) and not C1...
Steve

20080206, 19:59 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Paste special to create a formula (All versions)
Yes, that works. I had stupidly tried it with =C1 earlier today and got strange results  I should have known that the reference must be absolute.

20080206, 20:03 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Paste special to create a formula (All versions)
I had also "stupidly tried it with =C1" , but I deduced the problem/solution when I got #REF errors in the newly created formulas <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
Steve

20080206, 20:03 #11
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Paste special to create a formula (All versions)
You must have some magic form of Excel 2002?
I follow the instructions, copying D1 that contains =$C$1 and when i paste special in A1:A3, the values are:
A1  20
A2  40
A3  =(A1+A2)*2
I just cannot get the cell reference $C$1 to paste??????
What is happening with your magic version of Excel is EXACTLY what I was after. <img src=/S/sad.gif border=0 alt=sad width=15 height=15> I cannot get it!!!Regards,
Rudi

20080206, 20:19 #12
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Paste special to create a formula (All versions)
I am 100% sure I am doing it right! Thats the weird thing about it. Only difference is that I am in Office 2007; but surely that cannot be the problem!
A1  10
A2  20
A3  =A1+A2
C1  2
D1  =$C$1
I copy the *cell* D1 (not the text or the formula bar content!)
I select A1:A3
I right click in the selection and choose Paste Special
Paste is on ALL
I choose Multiple in the Operation Category
OK
Result:
A1  20
A2  40
A3  =(A1+A2)*2
<img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
Maybe someone else running Excel 2007 can test and see if they get the same result as me or if they can get it to work...Regards,
Rudi

20080206, 20:23 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Paste special to create a formula (All versions)
As I noted higher up, there are reports that it doesn't work in Excel 2007 (another example of "dumbing down" <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
See for example Paste Special  Formulas  Multiply.

20080206, 20:24 #14
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Paste special to create a formula (All versions)
<P ID="edit" class=small>(Edited by sdckapr on 06Feb08 14:24. Added PPS)</P>What are you doing exactly?
Are you copying the TEXT (from the formula bar) "=$C$1" from D1 (INCORRECT)
or are you selecting cell D1 (which contains the formula =$C$1), copying the cell (not its contents), and then selecting A1:A3, paste special  multiply...
Steve
PS you also must pastespecial with ALL (default) or FORMULAS checked. If Pastespecial  values and Multipy are checked you will get the "2" carried over, not the "$C$1"
PPS Make sure it is a formula in D1 and not " ' =$C$1"

20080206, 20:30 #15
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Paste special to create a formula (NOT 2007)
Grrrr....
This dumbing down is starting to irritate me!! Why did they do this. Rudi is very disappointed and highly frustrated!!! (Talking in third person with intent!)
Grrrr....
TX for clearing that up!Regards,
Rudi