formulas to numbers

[Steve]

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.)

[bill k]

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.

[Roger Govier]

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.)