If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
formulas to numbers
I want to create a table (by "table" I just mean a bunch of columns of
numbers and formulas laid side by side, I don't know whether "table" has a more technical meaning) where I manually enter a column of numbers, and update those numbers periodically. The table is of fixed size. I would like to create another table (on the same worksheet) that will record the history of one column of the first table, the column that I will be manually entering. For example, the first column of this second table will permanently record the values that the first column of the first table had on September 15, the second column of the second table will permanently record the values that the first column of the first table had on September 16, and so on. This second table will grow wider over time. Do I have to enter the numbers manually into the second table even after I have manually entered them into the first table, or is there a way to use formulas so that the numbers will automatically be entered into the second table? My concern is that if I use formulas then every column of the second table will change every time I enter updated numbers in the first table. I don't want every column to change, I just want to add a new column with the latest numbers while preserving the previous values of these numbers in the other columns. What might be a big help is if there were a way to turn a formula permanently into the constant number that it has calculated at a point in time. For example, if the formula in cell a10 is "=a1" and the current value of cell a1 is 42, is there a way to change cell a10 so that it will permanently record 42 and no longer be dependent on cell a1. (Of course, I would like to do this without having to erase the formula in cell a10 and manually enter 42 into cell a10, though this would work if there were no alternative.) |
#2
|
|||
|
|||
the normal method to change a formula into a value is to copy and paste special values. you can have a formula in k3 such as =if(k$2=$a$2,$b3,"") where k2 is the date on which you want to record the data a2 is today's date b3 is the number in your first table that you want to copy you can extend this formula across the page at the end of each "day" you can highlight the second table column that needs to be changed into values right clickcopyright click select paste specialselect paste values. this last routine could be done with a macro, but you would somehow need to find the right column first. -- bill k ------------------------------------------------------------------------ bill k's Profile: http://www.excelforum.com/member.php...nfo&userid=821 View this thread: http://www.excelforum.com/showthread...hreadid=468022 |
#3
|
|||
|
|||
Hi Steve
Why not just insert a new column to enter your new data. That way, your original set of entries would be preserved to the right of your newly input set of data. Just make the first cell in the column, the date when you are entering. Note. There is a limit of 255 columns to Excel. But when you are approaching this limit, just copy and paste the data to another sheet, delete the filled columns on Sheet1 and continue again. Regards Roger Govier Steve wrote: I want to create a table (by "table" I just mean a bunch of columns of numbers and formulas laid side by side, I don't know whether "table" has a more technical meaning) where I manually enter a column of numbers, and update those numbers periodically. The table is of fixed size. I would like to create another table (on the same worksheet) that will record the history of one column of the first table, the column that I will be manually entering. For example, the first column of this second table will permanently record the values that the first column of the first table had on September 15, the second column of the second table will permanently record the values that the first column of the first table had on September 16, and so on. This second table will grow wider over time. Do I have to enter the numbers manually into the second table even after I have manually entered them into the first table, or is there a way to use formulas so that the numbers will automatically be entered into the second table? My concern is that if I use formulas then every column of the second table will change every time I enter updated numbers in the first table. I don't want every column to change, I just want to add a new column with the latest numbers while preserving the previous values of these numbers in the other columns. What might be a big help is if there were a way to turn a formula permanently into the constant number that it has calculated at a point in time. For example, if the formula in cell a10 is "=a1" and the current value of cell a1 is 42, is there a way to change cell a10 so that it will permanently record 42 and no longer be dependent on cell a1. (Of course, I would like to do this without having to erase the formula in cell a10 and manually enter 42 into cell a10, though this would work if there were no alternative.) |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
My formulas can't 'see' numbers imported from Access in Excel, even when the format is the same. | Chrism | General Discussion | 4 | April 12th, 2005 03:10 PM |
FORMULAS FOR ADDING LETTERS REPRESENTING NUMBERS | koolone | General Discussion | 1 | April 8th, 2005 12:39 PM |
Can't see old phone numbers | rleblanc | Using Forms | 4 | August 28th, 2004 05:12 PM |
formulas for round numbers up to next dolllar | dewberryz | General Discussion | 2 | August 13th, 2004 02:45 PM |
Help Needed for Groups Please | Paul Black | General Discussion | 15 | June 21st, 2004 02:54 AM |