Cell Last Changed DateTime

I have built my own portfolio management spreadsheet, which pulls stoc
quotes from the web. What I'd like to do is to keep track of th
historical values of the portfolio.

Specifically, this is what I'm trying to do:
1) portfolio is on worksheet WS1, with a cell (call it A1) containing 
fomula which returns the current total of the whole portfolio,
2) history data is on worksheet WS2, with column A populated with 
series of dates, 2/15/2004, 2/16/2004, etc., and B (to be) populate
with the A1 of WS1 of that day,
3) whenever A1 of WS1 changes, I'd like to record the value of new A
into WS2, alongside the current date.

My only technical challange left in all this is to code some kind o
"cell changed" event on A1, which will then figure out what time it i
and where to write on WS2. I'm fairly decent at programming, so an
hint will help.

Thanks very much

--
Message posted from http://www.ExcelForum.com

0
2/18/2004 6:12:23 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
147 Views

Similar Articles

[PageSpeed] 41

A1 contains a formula?

Option Explicit
Private Sub Worksheet_Calculate()

    Dim LastCell As Range
    
    With Worksheets("ws2")
        Set LastCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(0, 1)
    End With
    
    With LastCell
        If .Value = Me.Range("a1").Value Then
            'do nothing
        Else
            With .Offset(1, -1) 'back to column A
                .Value = Now
                .NumberFormat = "mm/dd/yyyy hh:mm:ss"
            End With
            .Offset(1, 0).Value = Me.Range("a1").Value
        End If
    End With

End Sub

Each time WS1 calculates, it looks to see if the last row in ws2 (based on
column A) has the same value as the current value in A1.

If it does, it stops.

If not, then it does some assignments.


"TiVoSoFine <" wrote:
> 
> I have built my own portfolio management spreadsheet, which pulls stock
> quotes from the web. What I'd like to do is to keep track of the
> historical values of the portfolio.
> 
> Specifically, this is what I'm trying to do:
> 1) portfolio is on worksheet WS1, with a cell (call it A1) containing a
> fomula which returns the current total of the whole portfolio,
> 2) history data is on worksheet WS2, with column A populated with a
> series of dates, 2/15/2004, 2/16/2004, etc., and B (to be) populated
> with the A1 of WS1 of that day,
> 3) whenever A1 of WS1 changes, I'd like to record the value of new A1
> into WS2, alongside the current date.
> 
> My only technical challange left in all this is to code some kind of
> "cell changed" event on A1, which will then figure out what time it is
> and where to write on WS2. I'm fairly decent at programming, so any
> hint will help.
> 
> Thanks very much!
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
2/19/2004 3:04:24 AM
A couple of links that may help you later:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm
or
David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

"TiVoSoFine <" wrote:
> 
> I have built my own portfolio management spreadsheet, which pulls stock
> quotes from the web. What I'd like to do is to keep track of the
> historical values of the portfolio.
> 
> Specifically, this is what I'm trying to do:
> 1) portfolio is on worksheet WS1, with a cell (call it A1) containing a
> fomula which returns the current total of the whole portfolio,
> 2) history data is on worksheet WS2, with column A populated with a
> series of dates, 2/15/2004, 2/16/2004, etc., and B (to be) populated
> with the A1 of WS1 of that day,
> 3) whenever A1 of WS1 changes, I'd like to record the value of new A1
> into WS2, alongside the current date.
> 
> My only technical challange left in all this is to code some kind of
> "cell changed" event on A1, which will then figure out what time it is
> and where to write on WS2. I'm fairly decent at programming, so any
> hint will help.
> 
> Thanks very much!
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
2/19/2004 3:05:54 AM
Dave,

Thanks for the links. I'll definitely check them out

--
Message posted from http://www.ExcelForum.com

0
2/19/2004 3:16:40 PM
Reply:

Similar Artilces:

Ranking range of cell with value only
I am ranking set of numbers (# of days worked) in a 10 cell column but the numbers of persons I rank differs from day to day. If I enter "0"(zero) on one of the cell, it ranks the whole ten cells. I have to enter Zero because that is the number of days worked. How can I enter the value of zero that does not activate the other blank cells? Here is my formula: =IF(ISNA(RANK(B5,$B$5:$B$14,1)),"",RANK(B5,$B$5:$B$14,1)) Thanks. -- cardingtr ------------------------------------------------------------------------ cardingtr's Profile: http://www.excelforum.com/member.ph...

In excel how do i put zero as the first Number in a cell
how could i add zeros as the first number in a cell without a decimal Hi Two ways: Start your entry with an ', to read '083, or Format the cells where you want to insert this as text, and then enter the number "help" wrote: > how could i add zeros as the first number in a cell without a decimal > Either format the cell for TEXT (Right-click > FormatCells > NumberTab > Text > ok), or if you only have a few, just type a leading apostrophe before the leading zero.........it won't show up and will change that cell to TEXT format.......... Vaya con ...

change where sent messages are saved
Is it possible to change the folder where sent messages are saved in outlook 2002? I want to make this change for all sent messages and not just set on a message by message basis using options. I know this can be done for drafts and inbox, but I can't figure it our for sent items. Thanks, -Ben ...

Highlighting entire row and not just cell where cursor is placed
I have a large amount of data and was wondering if there was a way that the entire row would highlight vs just the cell that the cursor is in. For instance, I want the entire row of line 1115 to be highlighted (i.e. to stand out) as opposed to just the cell being highlighted. And, then, if I move to the next row, the entire row would be highltighted instead of just the cell my cursor is in. Thank you for any help. Nick Here is a write-up I have on what you want to do. HTH Otto To have an entire row highlighted to some color if any cell in that row is selected. Note that ...

Change Quote.dot
Can someone please tell me how the make changes to the Quote.dot. There is some text on the last page I need to change and for some reason I can not get past the merge fields to do this. Hi Valerie, Can you please help me understand exactly what text you need to change? -- Manisha Powar (Madhusudanan) Program Manager Microsoft Dynamics - CRM This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use. � 2005 Microsoft Corporation. All rights reserved. "Valerie" <Valerie@discussions.microsoft.com> wrote in mess...

Change in Open Behavior
Seems to me that after I had to reload my Office 2003 the open dialog box acts differently. I remember when I used to type in the name of the file I wanted to open, when it came up in the dialog box I could just hit enter and the file would open. Now when I hit enter it just isolates that file and I have to highlight it and double-click or hit enter. Has anyone seen this change? How can I get the old way back? Bruce ...

How to run an event upon changing the value of a specific cell?
I want to run an event or a mcaro only when a certain cell is changed. i.e., if the cell A1 value is changed, then do such and such ... Right click on your sheet tab, view code. Paste this in and modify to suit: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub 'Do something here End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Wael Fathy" wrote: > I want to run an event or a mcaro only when a certain cell is changed. > i.e., if ...

Changing CAPS to normal and vice-versa
Hi Please help me changing a cell's format from CAPS to normal and normal to CAPS using keyboard combinations. thanks -- Reg Deepak Hi there's no such command. Excel does not support this kind of formating (like Word does) -- Regards Frank Kabel Frankfurt, Germany Deepak wrote: > Hi > > Please help me changing a cell's format from CAPS to normal and > normal to CAPS using keyboard combinations. > > thanks Depends on what "normal" is to you. If you mean lower case, here's one way: Attach this to any keyboard shortcut: Public Sub Tog...

Changing Vendor address on already printed PO
When I printed a PO from the PO entry screen to the screen to preview I realized I had the wrong vendor address. I can change it in vendor cards but it won't update on the existing PO. Any way to get this to change or than creating a PO in excel? The only way to fix this is to cancel the PO (cancel all PO line quantities) and re-enter the PO with the correct address. Once a PO has been printed, you cannot Void or Delete it. Frank Hamelly MCP-GP, MCT East Coast Dynamics www.eastcoast-dynamics.com Penny, there are two ways you can change it. Just so you know, the vendor address...

Message icons have changed in Taskbar
Before - Opened Outlook messages displayed an "Outlook" icon in the taskbar Now - Opened Outlook messages display as "Word" icons in the taskbar Problem - the user cant tell the difference between open Outlook messages and open Word documents, they open multiple of each and quickly become confused, very annoyinig (the problem AND the user!!) Any ideas what may have caused this and how to "unwind" ? Outlook2003 latest SP and fully patched Thanks Apparently some recent patch caused it - I don't know which one, only that several people have complain...

How to change the spacing of data markers in a line/scatter chart
Is there a way to change the amount of data markers used in a line/scatter chart?? I have a lot of data, and it looks very messy... Thanks! ...

excel changes a string in a csv file to a date format! how to get around this?
hi experts... i have a csv file with a one field as follows: ........ ,"2300-1-6", ............... this is not a date. it is a string that i want to appear just like that. but in excel, it imports it as a date and changes it to 1/6/2300 i tried to change the cell format from date to general (or text), but this makes it appear as 146104. i tired to rename the .csv file to .txt and play with the import options, but nothing worked. can someone please help! thanks hilz Never mind. i found it. When the file is changed to txt file, the import options can handle it. i just did not know...

prompted to save when no changes
Hi ng Using xl 2003 I'm reviewing thousands of old spreadsheets to see if I should kee them or not. I open the book, take a quick look and close. I am asked if I would like to save changes, though I did nothing. What's going on -- Message posted from http://www.ExcelForum.com Hi There probably is a "voltatile" function in your worksheet (for instance: =NOW()) HTh Cordially Pascal "goss >" <<goss.18x81e@excelforum-nospam.com> a �crit dans le message de news:goss.18x81e@excelforum-nospam.com... > Hi ng > Using xl 2003 > > I'm review...

Drop Down in Cell?
I have a spreadsheet I created a while ago. It has cells with drop down lists... i.e., when in the cell, it shows a down-arrow/chevron to the right of the cell. Clicking on the down arrow gives a list of items to select from... based on the selection, other cells receive data from look-up tables. How did I create the drop down list in the cell? The cell has no formula, etc... just the behavior of the drop down. I can't remember how I did this.. Please help! Thanks MS Hi Martin Data > Validation. See De bra's page http://www.contextures.com/xlDataVal01.html on this. -- HTH...

How do I assign the value in a cell to a variable?
I'm sure this is easy for someone, TIA. I need a macro to search a range of cells (I can do that) and find a cell with a value (which can change) verses other cells that have no value. I can make the macro select the cell and copy it, but i need to assign the value the macro finds in that cell to a variable. Does the copied value in the clipboard have a name? Thanks again, Jason You can create a variable in VBA and assign it the value of the cell using the activeccell.value property You can assign value when you track the cell with value Your code could be dim x 'declare a variabl...

Account number change
Sorry for the repeat, but I'm still having trouble with this. My bank changed my account number. I had to change the credentials used for online updates. When Money updates, it creates a set of duplicate accounts for that bank. Fine...I merge duplicate accounts and everything should be fine, right? The next time money updates, it creates ANOTHER set of duplicate accounts that have no transactions in them. If I merge again, I can't see all my old transactions anymore. How do I correct this? In microsoft.public.money, Banjolicious wrote: >Sorry for the repeat, but I'm still ha...

Money Uk change to Quicken 2009
Just bought last weak Quicken 2010 to have an oppotunity to make my home finance, when money stops quote updating in the near future Working with money for more than a decade in a very sufficient way, it was very hard to change to quicken 2010. Money was simple and efficient. As Quicken is new to me, it is difficult to get the overview as fast, as I had it with money. But after several days using it, I get used to it. I was not able to transfer my investment accounts to Quicken, so I did it all, account by account manually. Today I am happy to have an alternative to money, but I would pr...

What need to change in asp.net if web server update from IIS6 to IIS7?
What need to change in asp.net application if web server update from IIS6 to IIS7? Our company just created a new web server using windows 2008 and iis7. -- Message posted via DotNetMonster.com http://www.dotnetmonster.com/Uwe/Forums.aspx/asp-net/201003/1 On Mar 2, 10:14=A0pm, "aspfun via DotNetMonster.com" <u53138@uwe> wrote: > What need to change in asp.net application if web server update from IIS6= to > IIS7? > Our company just created a new web server using windows 2008 and iis7. > > -- > Message posted via DotNetMonster.comhttp://www....

change column widths w/o affecting rows above
I'm trying to create a form and I want five columns of identical width on one row and then several rows below I want five columns of varying width. Is this possible? Thanks Not directly - width is a property of the column, not individual cells. You may be able to achieve the effect you want by merging cells. In article <C31E7155-1736-4136-A8F1-12F3DA4AD153@microsoft.com>, Mike G <anonymous@discussions.microsoft.com> wrote: > I'm trying to create a form and I want five columns of identical width on one > row and then several rows below I want five columns of v...

SUMIF cell is colored
I have cells in a column, some colored yellow, some not. I am trying to use the SUMIF function to sum only the cells in the range that are colored. Can this be done? Can I enter something in the "criteria" part of the formula that can do this? =SUMIF(D3:D13,"criteria",D3:D13) I also tried to use the CELL function's color feature, but I couldn't get it to work right. I don't know how to get Excel to recognize if a cell is colored in a formula. =CELL("color",cell) It might just be that I don't know what this means in Help: "color&q...

Changing the color of the follow-up Flag.
Can anyone tell me how to have multiple colors for the follow-up flag? I have been all over the help and the web trying to find an answer. Thank you in advance Tom Clark You can't in Outlook 2002 and earlier. Outlook 2003 adds this feature. -- Jeff Stephenson Outlook Development This posting is provided "AS IS" with no warranties, and confers no rights "Tom Clark" <donald.clark@gstleather.com> wrote in message news:0ade01c35ce7$2b94f910$a401280a@phx.gbl... > Can anyone tell me how to have multiple colors for the > follow-up flag? > > I ha...

Block colour a cell
i want to apply this to a cell, i dont know if it can be done if column O1:O370>4 and column L1:L370>11 I want cell AH to turn bright yellow If I understand you correctly, you mean that you want to color cell AH1 yellow if both O1 > 4 and L1 > 4, and likewise for each subsequent row down to AH370. If so, select AH1:AH370 (with AH1 the active cell) and choose Format/Conditional Formatting... Then use the dropdowns and textbox to enter Formula Is =AND(O1>4,L1>11) click Format, and choose bright yellow from the Patterns tab. Click OK, OK. XL will adjust the row re...

Two formulas in one cell
Correction: This did not work for what I need. I need to show the totals found on the second row, i.e. the totals of the 1.0, 2.0, etc. but keeping separate and together. I have an excel sheet showing what I need if I am too confusing here. Can you give us a simple example best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme of the data and the expected results? "something68" <something68@discussions.microsoft.com> wrote in message news:35ECB9C7-9B22-4CBA-9547-25801E3608DA@microsoft.com... > Correction: This did not work ...

multiple calculations in 1 cell
In cell C 13 is the quantity of units. In cell G 13 is the total cost. In cell G 6 is the percent of overhead and in cell G 8 is the percent of profit. I want to enter a formula in cell H 13 that will give me a unit price which is (cost plus overhead) + profit. Try this: =3D(G13*(1+G6)*(1+G8))/C13 Hope this helps. Pete On Mar 5, 9:10=A0pm, Pulling Hair <PullingH...@discussions.microsoft.com> wrote: > In cell C 13 is the quantity of units. =A0In cell G 13 is the total cost.= =A0In > cell G 6 is the percent of overhead and in cell G 8 is the percent of pro...

Printing Problem, Content not algned with Cells
Hi any idea how to fix this? The cell content is not aligned with the cell grid when printed. The first few cells are OK and then content gradually shifts, by the middle of the page the content is on top of the line etc. The printpreview looks OK. I tried different fonts with same result. Formatting seems OK. Creating a new sheet has the same effect, so it is not linked to a particular sheet. Copy and paste into word is OK. Printing the sheet on a differnt computer is OK too. Using different printers produces the same result. The problem seems to be with the Excel print engine. Office...