Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Gold Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  4. #4
    Gold Lounger
    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

  5. #5
    Gold Lounger
    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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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
    paste-special -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

  7. #7
    Gold Lounger
    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

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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

  11. #11
    Gold Lounger
    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

  12. #12
    Gold Lounger
    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

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  14. #14
    WS Lounge VIP sdckapr's Avatar
    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 06-Feb-08 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 paste-special with ALL (default) or FORMULAS checked. If Paste-special - 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"

  15. #15
    Gold Lounger
    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •