Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Steve
 
Posts: n/a
Default 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   Report Post  
bill k
 
Posts: n/a
Default


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

  #3   Report Post  
Roger Govier
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I set up formulas with letters and numbers? lisaw Excel Worksheet Functions 3 May 6th 05 10:05 PM
Sorting alphanumeric numbers maurice.centner Excel Discussion (Misc queries) 2 May 6th 05 02:00 AM
Formulas for telephone numbers: finding duplicates, autoformat Sandeep Elbak Excel Worksheet Functions 3 May 4th 05 07:59 AM
Sorting when some numbers have a text suffix confused on the tundra Excel Discussion (Misc queries) 5 December 18th 04 11:19 PM
finding common numbers in large lists Jenn Excel Worksheet Functions 1 November 11th 04 08:42 PM


All times are GMT +1. The time now is 01:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"