Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    allyn, Washington, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    add to column value, delete original column (2000 SP3)

    In column C, I enter people's ages. To keep the ages current, I want to add 1 year to each age at the start of the year. In cell D3, I enter formula =C3+1 to add one year to the value in column C, then copy the formula and paste it down column D to add the one year to all values that are in C. Now, I'd like to get rid of column C and keep only the values that are in Column D, thus the new D would become C. However, if I delete the old column C, I also lose the values in column D that were generated using the values in C.. Is there a way to keep only the new values and, in future years, update again?

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: add to column value, delete original column (2000 SP3)

    Right-drag and drop column D onto Column C. When the menu appears, choose "copy here as values only." Then delete column D. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: add to column value, delete original column (2000 SP3)

    Some suggestions:

    1) Enter the peoples' year of birth in column C instead of their ages.
    Enter the following formula in D3:
    =YEAR(TODAY())-C3
    and fill down as far as needed.
    The ages in column D will always be up to date.

    2) Enter the number 1 in a blank cell.
    Copy this cell to the clipboard.
    Select the cells with ages in column C.
    Select Edit | Paste Special...
    Click the Add option, then click OK.
    You can now clear the cell with 1 in it.

    3) Using your method:fill down the formula in D3 (=C3+1) as far as needed.
    With the cells still selected, copy them to the clipboard.
    Select Edit | Paste Special...
    Click the Values option and click OK.
    Now move the cells from column D to column C, overwriting the original values.

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: add to column value, delete original column (2000 SP3)

    BTW, an easier way to do this without adding a column is to enter a 1 in a blank cell, then copy this cell. Then select all of the ages & choose the Edit | Paste Special menu. Finally, in the dialog, click on Operation: Add & press OK. Then go back and delete the 1. The nice thing about this method is that it increments numbers, but changes formulas to =(old formula) + 1
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    allyn, Washington, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add to column value, delete original column (2000 SP3)

    Thanks to Sammy B & Hans for educating me. You make these operation quite simple.

Posting Permissions

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