Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Location
    Southern, New Hampshire, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving data from a formula cell (2000)

    Is there a way to save the data generated from a cell using a formula cell? EX: C2 contains the number 100. D2 contains the formula (C2*.5). This will generate the number 50 in D2. Now what I would like to do is keep the number "50" that was generated and delete the cell C2. Obviously when I delete C2, D2 being a formula cell has nothing to calculate. Can I save my new data "D2" to another cell without the need of the formula to get it?

    Any help is always appreciated.

    Thanks,

    Guy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving data from a formula cell (2000)

    Method 1:

    Select the cells with the formulas you want to get rid of.
    Copy them to the clipboard (Edit | Copy or Ctrl+C)
    Select Edit | Paste Special..., click Values, then OK.
    This method removes the formulas in place.

    Method 2:

    If you do this often, a macro to automate the above comes in handy. I created one in my Personal.xls and made it available through a custom toolbar button. Here is the code:

    Sub PasteValues()
    On Error GoTo EndIt
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    EndIt:
    End Sub

    Method 3:

    This works in Excel 2002, I don't know if it does in Excel 2000:

    Select the cells with the formulas you want to get rid of.
    Drag them to an empty location with the right mouse button.
    When you release the right mouse button, a context menu pops up.
    Select "Copy here as values only".
    Delete the cells with the formulas.

  3. #3
    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: Saving data from a formula cell (2000)

    If you don't need a special macro to copy and paste-values:
    Excel has a built-in toolbar button called "Paste values" that you can add to your toolbar.
    Then you can select the range, hit the copy button followed by the paste-values button to do it.

    I put the button next to the copy button.

    Steve

  4. #4
    New Lounger
    Join Date
    Nov 2002
    Location
    Southern, New Hampshire, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving data from a formula cell (2000)

    Thank you Hans and Steve. I tried your suggestions and added a paste value button to my tool bar. This will save me a lot of time.

    Guy <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Posting Permissions

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