update the same cell into another column forming a list on opening

hi there,
i have made a spreadsheet to enter data and then view the resulting 
statistics that are generated, i have one cell that changes in value after 
entering the data, and i would like to keep a record of what values this cell 
has been, i was after a formula that would update that single cell's value 
into a column forming a list each time the spreadsheet was opened or the 
value in the cell changed, like a field perhaps, this would fill up the 
column with a list of results from which to generate more stats and a graph 
etc.

any help would be great, thank you
-- 
thanks
0
Utf
12/9/2009 8:01:01 AM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
625 Views

Similar Articles

[PageSpeed] 56

This macro executes whenever the active sheet is calculated.  The macro 
looks at F1 of the active sheet and compares it to the last entry in Column 
A of a sheet named "Record".  If the two values are not the same, the macro 
places the value in F1 of the active sheet into the first blank cell in 
Column A of the "Record" sheet.  Note that nothing is done if the 2 values 
are the same.  Date and/or time values are not placed in "Record" because 
you didn't mention wanting that.  This macro is a sheet event macro and must 
be placed in the sheet module of the sheet with the changing F1 cell.  You 
can access that module by right-clicking the sheet tab and selecting View 
Code.  HTH  Otto
Private Sub Worksheet_Calculate()
    Dim Dest As Range
    With Sheets("Record")
        Set Dest = .Range("A" & Rows.Count).End(xlUp)
        If Range("F1") <> Dest Then _
            Dest.Offset(1) = Range("F1")
    End With
End Sub

"Morgan" <Morgan@discussions.microsoft.com> wrote in message 
news:4E69458B-0D52-4668-803E-2AC138A65CE8@microsoft.com...
> hi there,
> i have made a spreadsheet to enter data and then view the resulting
> statistics that are generated, i have one cell that changes in value after
> entering the data, and i would like to keep a record of what values this 
> cell
> has been, i was after a formula that would update that single cell's value
> into a column forming a list each time the spreadsheet was opened or the
> value in the cell changed, like a field perhaps, this would fill up the
> column with a list of results from which to generate more stats and a 
> graph
> etc.
>
> any help would be great, thank you
> -- 
> thanks 

0
Otto
12/9/2009 3:45:31 PM
thanks it works great, you mentioned the date and it sounded like a good 
idea, what would the code be to have the date in column A and the entries in 
column B?
-- 
thanks


"Otto Moehrbach" wrote:

> This macro executes whenever the active sheet is calculated.  The macro 
> looks at F1 of the active sheet and compares it to the last entry in Column 
> A of a sheet named "Record".  If the two values are not the same, the macro 
> places the value in F1 of the active sheet into the first blank cell in 
> Column A of the "Record" sheet.  Note that nothing is done if the 2 values 
> are the same.  Date and/or time values are not placed in "Record" because 
> you didn't mention wanting that.  This macro is a sheet event macro and must 
> be placed in the sheet module of the sheet with the changing F1 cell.  You 
> can access that module by right-clicking the sheet tab and selecting View 
> Code.  HTH  Otto
> Private Sub Worksheet_Calculate()
>     Dim Dest As Range
>     With Sheets("Record")
>         Set Dest = .Range("A" & Rows.Count).End(xlUp)
>         If Range("F1") <> Dest Then _
>             Dest.Offset(1) = Range("F1")
>     End With
> End Sub
> 
> "Morgan" <Morgan@discussions.microsoft.com> wrote in message 
> news:4E69458B-0D52-4668-803E-2AC138A65CE8@microsoft.com...
> > hi there,
> > i have made a spreadsheet to enter data and then view the resulting
> > statistics that are generated, i have one cell that changes in value after
> > entering the data, and i would like to keep a record of what values this 
> > cell
> > has been, i was after a formula that would update that single cell's value
> > into a column forming a list each time the spreadsheet was opened or the
> > value in the cell changed, like a field perhaps, this would fill up the
> > column with a list of results from which to generate more stats and a 
> > graph
> > etc.
> >
> > any help would be great, thank you
> > -- 
> > thanks 
> 
> .
> 
0
Utf
12/10/2009 4:21:02 AM
Note that I chose a date format of 14-Mar-09.  That is just my preference. 
You can record a macro and format a cell to your preference and change the 
code accordingly.  The date format comes after "NumberFormat" in the code. 
Otto
Private Sub Worksheet_Calculate()
    Dim Dest As Range
    With Sheets("Record")
        Set Dest = .Range("A" & Rows.Count).End(xlUp)
        If Range("F1") <> Dest Then _
            Dest.Offset(1) = Range("F1")
            Dest.Offset(1, 1) = Date
            Dest.Offset(1, 1).NumberFormat = "d-mmm-yy"
    End With
End Sub

"Morgan" <Morgan@discussions.microsoft.com> wrote in message 
news:6D179B39-03BE-417E-80ED-F5007236B1F5@microsoft.com...
> thanks it works great, you mentioned the date and it sounded like a good
> idea, what would the code be to have the date in column A and the entries 
> in
> column B?
> -- 
> thanks
>
>
> "Otto Moehrbach" wrote:
>
>> This macro executes whenever the active sheet is calculated.  The macro
>> looks at F1 of the active sheet and compares it to the last entry in 
>> Column
>> A of a sheet named "Record".  If the two values are not the same, the 
>> macro
>> places the value in F1 of the active sheet into the first blank cell in
>> Column A of the "Record" sheet.  Note that nothing is done if the 2 
>> values
>> are the same.  Date and/or time values are not placed in "Record" because
>> you didn't mention wanting that.  This macro is a sheet event macro and 
>> must
>> be placed in the sheet module of the sheet with the changing F1 cell. 
>> You
>> can access that module by right-clicking the sheet tab and selecting View
>> Code.  HTH  Otto
>> Private Sub Worksheet_Calculate()
>>     Dim Dest As Range
>>     With Sheets("Record")
>>         Set Dest = .Range("A" & Rows.Count).End(xlUp)
>>         If Range("F1") <> Dest Then _
>>             Dest.Offset(1) = Range("F1")
>>     End With
>> End Sub
>>
>> "Morgan" <Morgan@discussions.microsoft.com> wrote in message
>> news:4E69458B-0D52-4668-803E-2AC138A65CE8@microsoft.com...
>> > hi there,
>> > i have made a spreadsheet to enter data and then view the resulting
>> > statistics that are generated, i have one cell that changes in value 
>> > after
>> > entering the data, and i would like to keep a record of what values 
>> > this
>> > cell
>> > has been, i was after a formula that would update that single cell's 
>> > value
>> > into a column forming a list each time the spreadsheet was opened or 
>> > the
>> > value in the cell changed, like a field perhaps, this would fill up the
>> > column with a list of results from which to generate more stats and a
>> > graph
>> > etc.
>> >
>> > any help would be great, thank you
>> > -- 
>> > thanks
>>
>> .
>> 
0
Otto
12/10/2009 6:57:23 PM
Sorry about that.  I didn't catch the sequence you said (date in A and entry 
in B).  The following works like you want.  Otto
Private Sub Worksheet_Calculate()
    Dim Dest As Range
    With Sheets("Record")
        Set Dest = .Range("A" & Rows.Count).End(xlUp)
        If Range("F1") <> Dest Then _
            Dest.Offset(1, 1) = Range("F1")
            Dest.Offset(1) = Date
            Dest.Offset(1).NumberFormat = "d-mmm-yy"
    End With
End Sub


"Morgan" <Morgan@discussions.microsoft.com> wrote in message 
news:6D179B39-03BE-417E-80ED-F5007236B1F5@microsoft.com...
> thanks it works great, you mentioned the date and it sounded like a good
> idea, what would the code be to have the date in column A and the entries 
> in
> column B?
> -- 
> thanks
>
>
> "Otto Moehrbach" wrote:
>
>> This macro executes whenever the active sheet is calculated.  The macro
>> looks at F1 of the active sheet and compares it to the last entry in 
>> Column
>> A of a sheet named "Record".  If the two values are not the same, the 
>> macro
>> places the value in F1 of the active sheet into the first blank cell in
>> Column A of the "Record" sheet.  Note that nothing is done if the 2 
>> values
>> are the same.  Date and/or time values are not placed in "Record" because
>> you didn't mention wanting that.  This macro is a sheet event macro and 
>> must
>> be placed in the sheet module of the sheet with the changing F1 cell. 
>> You
>> can access that module by right-clicking the sheet tab and selecting View
>> Code.  HTH  Otto
>> Private Sub Worksheet_Calculate()
>>     Dim Dest As Range
>>     With Sheets("Record")
>>         Set Dest = .Range("A" & Rows.Count).End(xlUp)
>>         If Range("F1") <> Dest Then _
>>             Dest.Offset(1) = Range("F1")
>>     End With
>> End Sub
>>
>> "Morgan" <Morgan@discussions.microsoft.com> wrote in message
>> news:4E69458B-0D52-4668-803E-2AC138A65CE8@microsoft.com...
>> > hi there,
>> > i have made a spreadsheet to enter data and then view the resulting
>> > statistics that are generated, i have one cell that changes in value 
>> > after
>> > entering the data, and i would like to keep a record of what values 
>> > this
>> > cell
>> > has been, i was after a formula that would update that single cell's 
>> > value
>> > into a column forming a list each time the spreadsheet was opened or 
>> > the
>> > value in the cell changed, like a field perhaps, this would fill up the
>> > column with a list of results from which to generate more stats and a
>> > graph
>> > etc.
>> >
>> > any help would be great, thank you
>> > -- 
>> > thanks
>>
>> .
>> 
0
Otto
12/10/2009 7:02:22 PM
thanks Otto



"Otto Moehrbach" wrote:

> Sorry about that.  I didn't catch the sequence you said (date in A and entry 
> in B).  The following works like you want.  Otto
> Private Sub Worksheet_Calculate()
>     Dim Dest As Range
>     With Sheets("Record")
>         Set Dest = .Range("A" & Rows.Count).End(xlUp)
>         If Range("F1") <> Dest Then _
>             Dest.Offset(1, 1) = Range("F1")
>             Dest.Offset(1) = Date
>             Dest.Offset(1).NumberFormat = "d-mmm-yy"
>     End With
> End Sub
> 
> 
> "Morgan" <Morgan@discussions.microsoft.com> wrote in message 
> news:6D179B39-03BE-417E-80ED-F5007236B1F5@microsoft.com...
> > thanks it works great, you mentioned the date and it sounded like a good
> > idea, what would the code be to have the date in column A and the entries 
> > in
> > column B?
> > -- 
> > thanks
> >
> >
> > "Otto Moehrbach" wrote:
> >
> >> This macro executes whenever the active sheet is calculated.  The macro
> >> looks at F1 of the active sheet and compares it to the last entry in 
> >> Column
> >> A of a sheet named "Record".  If the two values are not the same, the 
> >> macro
> >> places the value in F1 of the active sheet into the first blank cell in
> >> Column A of the "Record" sheet.  Note that nothing is done if the 2 
> >> values
> >> are the same.  Date and/or time values are not placed in "Record" because
> >> you didn't mention wanting that.  This macro is a sheet event macro and 
> >> must
> >> be placed in the sheet module of the sheet with the changing F1 cell. 
> >> You
> >> can access that module by right-clicking the sheet tab and selecting View
> >> Code.  HTH  Otto
> >> Private Sub Worksheet_Calculate()
> >>     Dim Dest As Range
> >>     With Sheets("Record")
> >>         Set Dest = .Range("A" & Rows.Count).End(xlUp)
> >>         If Range("F1") <> Dest Then _
> >>             Dest.Offset(1) = Range("F1")
> >>     End With
> >> End Sub
> >>
> >> "Morgan" <Morgan@discussions.microsoft.com> wrote in message
> >> news:4E69458B-0D52-4668-803E-2AC138A65CE8@microsoft.com...
> >> > hi there,
> >> > i have made a spreadsheet to enter data and then view the resulting
> >> > statistics that are generated, i have one cell that changes in value 
> >> > after
> >> > entering the data, and i would like to keep a record of what values 
> >> > this
> >> > cell
> >> > has been, i was after a formula that would update that single cell's 
> >> > value
> >> > into a column forming a list each time the spreadsheet was opened or 
> >> > the
> >> > value in the cell changed, like a field perhaps, this would fill up the
> >> > column with a list of results from which to generate more stats and a
> >> > graph
> >> > etc.
> >> >
> >> > any help would be great, thank you
> >> > -- 
> >> > thanks
> >>
> >> .
> >> 
> .
> 
0
Utf
12/10/2009 9:44:01 PM
Reply:

Similar Artilces:

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

Unable to open .docx documents in Word 2002
I cannot open word documents sent with .docx extendion in word 2002. I checked my language setting and it is English. What is wrong? Download the Compatibility Pack. http://www.microsoft.com/downloads/details.aspx?familyid=941b3470-3ae9-4aee-8f43-c6bb74cd1466&displaylang=en -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Johnpm" <Johnpm@discussions.microsoft.com> wrote in message news:43E248E8-5D83-4E91-9743-CE12CCD6A443@microsoft.com... >I cannot open word documents sent with .docx extendi...

Stop My Documents from opening all the time?
Is there anyway to stop My Documents from opening everytimg I start a Windows Explorer session? I guess there's no future in trying to delete it, because so many products try to put stuff there. Right? Laurel wrote: > Is there anyway to stop My Documents from opening everytimg I start a > Windows Explorer session? > If you want Explorer to open somewhere else, simply modify the properties of the shortcut you're using to open it. All you need do is create/modify the target field of your Windows Explorer shortcut to point where you want it. Co...

More Columns #2
I am using all columns through IV on one of my worksheets. Does anyone know how to get more columns? Hi stacy know way using Excel. this is the maximum! -- Regards Frank Kabel Frankfurt, Germany Stacy Haskins wrote: > I am using all columns through IV on one of my > worksheets. Does anyone know how to get more columns? As Frank said, that's the maximum cols But maybe you could try transposing your table / data ? (Excel has a lot more rows than columns) If so, try: http://tinyurl.com/2nmyy for an example on using TRANSPOSE() For a one-time Tranpose: Copy > Paste Specia...

owa cant open ppt attachments
i have an exchange 2003 cluster when a user tries to open or save a powerpoint attachment it hangs at 97% all other attachments open fine any ideas thanks ...

Cannot open Outlook, keep getting Send error report of Microsoft.
We are having problems with Outlook 2003. Everytime we try to open, we get this: Send error report to Microsoft. Any ideas why? All windows and office update have been completed. ...

Excel corrupts when asking to update vlookups
We are experiencing weird behavior with some Office 2K3 Excel spreadsheets that contain lots of calculations, but no macros. On some pc’s Excel acts normally, on others you get the error. I have a couple of screen shots available. Any help is appreciated. If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Gu...

Columns #4
My columns in Excel have disappeared how do I get them back? Hi Me! I'm just guessing what you mean. Use: Tools > Options > General Remove check from "R1C1 reference style" OK -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "me" <anonymous@discussions.microsoft.com> wrote in message news:12D9050B-079C-476D-B5B6-84445F60EA51@microsoft.com... > My columns in Excel have disappeared how do I get them back? Another gue...

contact list #13
Good Day, I hope I am in the right area, we are running Exchange 2003 one Server 2003 and have Outlook 03 clients. We have address lists in the public folder an admin and a vendor, these contain contact information on contrators and vendors. In the folder list I can browse down and few and click on a contact to send them an email, but when I open a new email to send and I click on to and what to chose the list from the address book drop down box they are not there. How do I set it up so vendors and Admin contact list appear in the drop down box in the contact list? Thank you David ...

explanation of codes in Visual Basic when creating User form
Hi, I am trying to create a user form in Visual Basic however I'm trying to teach myself by reading/watching tutorials. (www.contectures.o.ca, etc) A lot of the instructions I am seeing simply give the code rather than explain how to actually write one from scratch. So... I need to know what each 'term' means so I can understand how the codes work. Any help is much appreciated :) One of the first codes is for the Add button Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("PartsData") What d...

how to find an item in a list control?
hi all, In a list Control how to find an item. If it is found then the item should be highlighted. how to do this? i wrote the following code but its not working what's wrong in the code? LVFINDINFO info; int nIndex; info.flags = LVFI_PARTIAL|LVFI_STRING; info.psz = (LPCTSTR) str; //fd.m_strEditCtrl; sprintf(st,"%d",m_ListCtrl.GetItemCount()); MessageBox(st,"Count"); for(int i=0;i < m_ListCtrl.GetItemCount();i++) { if((nIndex = m_ListCtrl.FindItem(&info,-1)) == -1 ) { m_ListCtrl.SetItemState(nIndex,LVIS_SELECTED,LVIS_SELECTED); } } thanks and r...

default values in a cell
Hello, can you help me please Cell B1 contains a complex mathematical formula which requires (in several places) a number from cell A1. Cell A1 can contain any integer number, but it is usually the same (30). I would like to be able to leave cell A1 empty, and only enter a number when it is not 30 , ie the default value of A1 is 30, unless specified. How do I go about this ? Should I look at conditional formatting, or put lots of IF functions into an already complex formula? Thank as always KK Use 2 cells Modify the complex formula to use B1 rather than A1 ( or any other un-u...

cannot open hyper links in outlook
when I try to open a hyperlink in outlook, I get the following message: This operation has been cancelled due to restrictions in effect on this computer. Please contact your system administrator. ----- I am the system administrator. HELP This is a problem with IE, not Outlook. You need to reset your internet settings in IE's Tools, Internet Options, Advanced tab. (Or Control Panel, Internet options, Advanced tab). See http://www.slipstick.com/problems/link_restrict.htm for more information. "Donald McNeely" <Donald McNeely@discussions.microsoft.com>...

Show date, time & day of week in one cell
Can I show the date, time and day of week in one cell. I have: 09/03/07 8:30 AM in one cell using the format (Format/Cells/Custom): [$-409]mm/dd/yy h:mm AM/PM;@ Excel refuses to accept ddd for Mon or dddd Monday at the end of the format I want it to read: 09/03/07 8:30 AM Monday in 1 cell. I have Excel 2003. One way: mm/dd/yy h:mm AM/PM dddd;@ In article <Xns99B8A3CAF9130pencilunlistedcom@208.49.82.220>, Burp <burp@beep.comINVALID> wrote: > Can I show the date, time and day of week in one cell. > > I have: > 09/03/07 8:30 AM > in one cell using the for...

How to automate increasing the form cache registry/file etc...
I want to roll out a batch file to make a number of tweaks to CRM The body of it would go REGEDIT /S Kerberosefix.reg REGEDIT /S ForceFormreload.reg REGEDIT /S OutlookFix.reg It would also rename OSA.exe to OSA.bad Remove OSA.exe From the startup menu I need help finding a way to use my batch file to increase the Outlook Form cache from the default 4MB to 50 MB.. This makes CRm more stable and faster for communications. I dont want to manually do this, as it time consuming, are my end users would not be reliable in doing it themselves. I also want to make another batch file or button that...

AR invoicing update GL but not receivables
We have an issue where the GL is updated with invoicing activity, but in some cases the receivables side is not. Any piror issues with this, advice on how to figure out the problem, etc? Thanks I have only heard of the opposite happening - AR subledger is updated, but the GL is not. Can you walk through exactly what happens? What type of AR document in what screen? What is the 'On Account' amount? What are the GL distributions? Where are you going to see that the AR subledger is not updated? Where do you see that the GL is updated? -- Victoria Yudin Microsoft MVP - Gre...

Security Updates-Uninstall
I was wondering if anyone knows how to backdown security updates? We ran the latest security updates for our servers and now most of our applications won't run properly. Has anyone had this experience before? Can we back these down? Cheers. Control Panel -> Add remove programs ??? "Geoff" wrote: > I was wondering if anyone knows how to backdown security > updates? We ran the latest security updates for our > servers and now most of our applications won't run > properly. Has anyone had this experience before? Can we > back these down? > ...

form fields not saving to table
I have created a database with a form and three of the fields are not saving the information to the table. I have checked the row and control sources and now at a loss and thoughts? As you scroll through existing records is the data of the table displayed? Post the SQL of the query or form source. -- Build a little, test a little. "Kim" wrote: > I have created a database with a form and three of the fields are not saving > the information to the table. I have checked the row and control sources and > now at a loss and thoughts? > > ...

Call & Place Graphic Based on Cell Value?
Is there any way to call & place a graphic image based upon a cell value? Maybe you can look at J.E. McGimpsey's page: http://www.mcgimpsey.com/excel/lookuppics.html documike wrote: > > Is there any way to call & place a graphic image based upon a cell value? -- Dave Peterson ...

Bulk Import
Hi All. I have a need to import leads (about 20,000 of them) and then I need to update each lead (with data from an outsourced Telemarketing supplier) every 2 weeks. I will create new attributes every 2 weeks for the lead to match the data coming back from my supplier but would I be able to "update" my existing 20,000 leads and not re-import as I would like to keep the activity history. Any idea how I could do this? Thanks in advance. ...

Automatically copy input from one cell to another
After I enter a value in one cell, how can I have it automatically enter it into another cell, within the same worksheet, or into a different worksheet. Thanks, Tom picktr@wowway.co -- Message posted from http://www.ExcelForum.com If you enter the value in A1 of sheet1, put this in the other cell in sheet1: =A1 or in another worksheet: =Sheet1!A1 In article <picktr.15c6uy@excelforum-nospam.com>, picktr <<picktr.15c6uy@excelforum-nospam.com>> wrote: > After I enter a value in one cell, how can > I have it automatically enter it into another cell...

Is there a way to cut off unused cells on a sheet
It seems there are an infinite number of cells on a sheet. As I really dont have much info to enter on each sheet I was hoping there was a way I could somehow cut off all the extra stuff to the sides and bottoms. It is a hassle because everytime I scroll, it will scroll off the side or bottom way past what I was looking for. Thanks! A manual way is to goto the last used row and delete all rows below. Do the same with columns. SAVE -- Don Guillett SalesAid Software donaldb@281.com "newbie" <newbie@discussions.microsoft.com> wrote in message news:469AABE7-8D17-4B72-91C1-...

Formating text in cells
Hello, does anyone know how to force Excel to keep the hyphen or dash character (-) with the associated numbers when putting multiple dash-#s in a cell and turning "wrap" formating on? This is the same problem I have in Word; when I add dash-#s (ie; -xxx, -yyy, -zzz) Office apps interpret dashes ( - ) as a hypehen to a word and will keep the dash/hyphen on the line above until there are enough characters to force the dash/hyphen to the next line. In my work we use the format; -xxx, -yyy, -zzz to denote multiple versions of the same base part. Work instructions to operators...

form base authentication #2
This is killing me. I'm implementing form base authentication. I have SSL and everything working but for one problem when use won't to get to owa they type mail.domain.com which then defaults to http://mail.domain.com. the problem I'm having is that FBA need it to default to https not http. How can I get it to default to HTTPS with out telling the uses to type https? I am implementing this on a FE/BE environment wayne .. What I do is change the http port on the main site to something like 8080, and then create a new site that listens on 80. For that site, instead of...

Query by Form-TJ
I need to do a Query by Form based on multiple selections from combo boxes on a form using Access 2000 I need to bring back the appropriate records in a subform. On Mon, 31 May 2010 07:08:01 -0700, ambushsinger <ambushsinger@discussions.microsoft.com> wrote: Query by Form can't do that. It can only select records in the current form view. After that, you can potentially open a new form with that subset of records in a subform. -Tom. Microsoft Access MVP >I need to do a Query by Form based on multiple selections from combo boxes on >a form using Access 200...