in other words you want to record the value in A20 in another cell one a day
at time = 00:00.
do you want it to record in a different cell each day, or do you copy out
the value from BT10 each day yourself?

right click on the tab
select this workbook section and try something like

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Static olddate As Date
If Date < olddate Then
Range("BT10") = Cells(20, 1)
olddate = Date
End If
End Sub


End Sub



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