On Thu, 06 Oct 2005 16:17:49 GMT, "Ronald Lawrence"
wrote:

I am tying to develop this formula in cell BT10:

=IF(TODAY()=BT8,Main!$A$20,converted value of $A$20 at midnight)

Where BT8 is today's date in a row of week days (for full year)
Where $A$20 is the dynamic cell in a Sheet called "Main" - this is
Cinderella
BT10 is the cell where I want the value of $A$20 to be recorded at midnight.
I have been playing with VALUE(Main!$A20) in the "false" condition area but
that hasn't been working for me.

The problem is I don't know how to convert the $A$20 cell on condition
(false) to its value only.

Basically, Main!$A$20 is the cell where the sum of the price of shares which
change throughout the day (typed in hourly for me). I want BT10 to record
its value at midnight automatically for trend graphing purposes and so that,
the next day, Main!$A$20 is working on cell BS10 (relating to the date at
BS8) and so on.

Obviously, since I am trend graphing I want this formula to apply to many
similar cells BT11, BT12, etc down the column for that date. Presently,
because I can't get the formula to work, I have to type in the value of all
these cells at the end of the day. It's not a huge chore (only about twenty
cells) but I would like the satisfaction of having it done automatically and
of knowing how to program this in future.

Thanks for your response Ron, hope you can help.


Hmm.

Not sure exactly how your data is set up. It sounds as if your dates start in
BT8 and then proceed to the left. Of course, that doesn't leave you room for a
full years trading dates, so maybe I'm missing something.

In any event, it seems you are going to need VBA to do what you want. Probably
an event macro that looks at the value in Main!A20 when it changes, and just
writes it into the appropriate cell in your BT range.

You should be able to modify the following to work with your layout:

Right click on the Main sheet tab and select View Code.

Paste the code below into the window that opens.

Basically, if A20 changes, it writes the value into the cell in row 10 that
corresponds with TODAY's date on your system clock. So when the last entry is
made TODAY, that value will be placed in the cell corresponding to TODAY.

=========================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range
Dim Dts As Range
Dim c As Range

Set AOI = [$A$20]
Set Dts = Worksheets("Sheet2").Range("BT8:B8")

If Not Intersect(Target, AOI) Is Nothing Then
With Dts
Set c = .Find(Date, LookIn:=xlValues)
If c Is Nothing Then Exit Sub
End With
c.Offset(2, 0).Value = Target.Value
End If

End Sub
=======================================

I don't know where you are getting your stock quotes from. But if they are
carried on MSN Money, you might be able to use Microsofts MSN Money Stock Quote
add-in to help automate the process.

===============================

--ron