A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

formulas to numbers



 
 
Thread Tools Display Modes
  #1  
Old September 15th, 2005, 08:28 PM
Steve
external usenet poster
 
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  
Old September 15th, 2005, 09:46 PM
bill k
external usenet poster
 
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 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  
Old September 16th, 2005, 12:07 AM
Roger Govier
external usenet poster
 
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.)

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


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


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