+ Reply to Thread
Results 1 to 12 of 12

how to change a calculated cell to a value

  1. #1
    Registered User
    Join Date
    06-17-2006
    Posts
    28

    how to change a calculated cell to a value

    I am entering data for a month on one worksheet. The next month I change the date and reenter new data. I want another worksheet to record the data on a month to month basis, one month in one column, the other month in another column, to have year to date. Is there anyway to do this. Currently, I use an if statment =If(a1="January",b1), but I need to change these to values before I do the next month, and alter the formula to reflect the new month name)

    thanks

  2. #2
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848
    Select the cells and go to Edit / Copy. Go to Edit / Paste Special... then choose Values.
    Copy and paste Special are also available by using a right click.

  3. #3
    Registered User
    Join Date
    06-17-2006
    Posts
    28
    Is there any way to automate it. I guess I could imbed a bunch of IF statements

  4. #4
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848
    Can you post an example of the workbook? (version 2003 or earlier) Please provide a more detailed explaination of what you would like to accomplish.

  5. #5
    Valued Forum Contributor
    Join Date
    08-26-2008
    Location
    singapore
    Posts
    626
    There's no such a formula to copy a formula to value, unless you want to use macro.

    Why not change your formula to

    Please Login or Register  to view this content.
    then you don't need to waste too much time on change your forumla every time
    I need your support to add reputations if my solution works.


  6. #6
    Registered User
    Join Date
    06-17-2006
    Posts
    28
    I've attached a sample spreadsheet. Under the Data tab, all I want to do is change the date, and enter my new numbers. Under the summary, I want to see all the months I've entered.

    Sorry for not being to clear.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-17-2006
    Posts
    28
    I guess what I want is Excel to work like a database program. Have one data entry screen, but have be smart enough to put the information in the correct columns (by month) and not lose what was already entered.

    Warren

  8. #8
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848
    If you want Excel to act as a database, you'll need to set up your data sheet more like a database. You need to go vertical

    Data Sheet
    Please Login or Register  to view this content.
    Summary sheet uses the following formula for transferring the data
    Please Login or Register  to view this content.
    See attached
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    06-17-2006
    Posts
    28
    I like how you set that up, but I'm stuck in that my data entry worksheet wont allow multiple months. I usually have lots of data to enter, plus I have data on multiple worksheets. The summary page is a snapshop of our total operations, from number of customers to our Net Income for the month. I use the one spreadsheet to capture all of my monthly data, financial and otherwise on numerous worksheets and then summarize it all for the board, on a month by month basis, showing previous months. I was hoping to eliminate all of this double entry, hoping that if I've already entered it on the data page, that it would automatically transfer over to the summary.

  10. #10
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848
    I would need to see a more representative case in order to help more properly

    If you always use the same Data / Summary sheets you could use the following macro:

    Please Login or Register  to view this content.
    Highlight the column you whose data you want to retain and whose formula you would want to copy one column to the right then run the macro.

    New version with macro attached
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    06-17-2006
    Posts
    28
    hey, that works really good. I'll give it a try tomorrow.

    thank you

  12. #12
    Forum Contributor
    Join Date
    12-19-2006
    Posts
    113
    Hi Warrenc,

    Try the attached file...
    Attached Files Attached Files

+ 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. Change cell value based on cell color?
    By Excel_Drone_VII in forum Excel General
    Replies: 14
    Last Post: 05-31-2013, 11:11 AM
  2. Replies: 2
    Last Post: 04-11-2008, 02:11 AM
  3. Change Cell Value
    By shguser in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-07-2008, 04:47 PM
  4. Change populated cell names via macro
    By TJM in forum Excel General
    Replies: 2
    Last Post: 09-12-2006, 10:47 PM
  5. change color and value of cell by formula
    By jurjon in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-12-2006, 11:00 AM

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