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.) |
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 click<copy<right click< select paste special<select 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 |
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.) |
All times are GMT +1. The time now is 05:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com