+ Reply to Thread
Results 1 to 8 of 8

Excel Formula Copy Paste Issue

  1. #1
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2007
    Posts
    107

    Excel Formula Copy Paste Issue

    Hi Everyone!
    I have long excel formula which takes different values from different cells. I want that when I copy a formula to paste it somewhere else then at that time then some cells' actual values should be present in the formula instead of cell ID. Like I have shown in the attachment, actual formula is =Sin(A1)+Cos(C1) but at the time of copying the same formula I want the copied fomula should be like Sin(150)+Cos(C1) where actual value of A1 is present.

    I hope you understand my problem easily.

    I will really appreciate your help in this regard.

    Regards

    Snapshot Excel Formula to Value - Copy.PNG

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,297

    Re: Excel Formula Copy Paste Issue

    Do you mean that, no matter where you paste the formula, it always refers back to A1 and C1???

    if so,

    =SIN($A$1)+COS($C$1)
    Glenn



  3. #3
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2007
    Posts
    107

    Re: Excel Formula Copy Paste Issue

    No, I mean, part of the formula should refer back to the actual value which is 150 in case of given example. So while pasting the formula the result should be =SIN(150)+COS($C$1) .

    In your case if the $A$1 value changes then my formula also changes and that is what I want to avoid. And that is why I want to import the actual value.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,437

    Re: Excel Formula Copy Paste Issue

    You cannot use a formula to just change a cell ref to a value.

    If A1 changes to something else, then the original formula result will also change. It sounds to me like you need something to "remember" what the contents of A1 was before it was changed? A formula cant do that
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,932

    Re: Excel Formula Copy Paste Issue

    How about use range name refers to a constant value?
    Attached Images Attached Images

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,437

    Re: Excel Formula Copy Paste Issue

    Quote Originally Posted by Bo_Ry View Post
    How about use range name refers to a constant value?
    Maybe Im reading it wrong, but sounds to me like the OP wants to have (eg) 150 in A1, then copy the formula that references A1 to instead, now show 150, so that when the 150 changes, the formula does not reflect the changed value

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,031

    Re: Excel Formula Copy Paste Issue

    In other cell, try:
    =SUBSTITUTE(SUBSTITUTE(FORMULATEXT(B1),"A1",A1),"C1",C1)
    Attached Images Attached Images

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,932

    Re: Excel Formula Copy Paste Issue

    Quote Originally Posted by FDibbins View Post
    Maybe Im reading it wrong, but sounds to me like the OP wants to have (eg) 150 in A1, then copy the formula that references A1 to instead, now show 150, so that when the 150 changes, the formula does not reflect the changed value
    Maybe I'm wrong and I still don't understand the OP yet, just guessing around.

    Maybe try UDF

    Please Login or Register  to view this content.
    FMref.png
    Attached Files Attached Files
    Last edited by Bo_Ry; 11-23-2020 at 04:22 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy/Paste Formula in VBA - Formatting Issue
    By Gtrtim112 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-28-2020, 12:58 PM
  2. Excel Copy and paste issue.
    By dee2016 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-04-2016, 09:52 PM
  3. [SOLVED] Excel copy/paste formula issue
    By killerthun in forum Excel General
    Replies: 2
    Last Post: 10-29-2013, 10:16 AM
  4. Copy/Paste from Excel to Website Issue
    By boswelljw in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-10-2013, 09:09 AM
  5. Excel 2007 copy/paste issue
    By pollyanna80 in forum Excel General
    Replies: 2
    Last Post: 08-12-2012, 05:22 PM
  6. Copy/Paste Issue and Formula Help
    By Josh55303 in forum Excel General
    Replies: 0
    Last Post: 07-18-2011, 02:31 PM
  7. Excel Copy and Paste Issue
    By Joseph Cura in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-18-2006, 06:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1