Changing values daily

[George Gee]

I would like to know if this is achievable?

I would like cell B30 to show whatever is in cell F10
If the value in cell F10 changes, then the new value
to be displayed in B31, cell B30 still showing the
original value, and so on down the sheet.

The value in F10 is the result of a formula =SUM(C10:E10)

Or, possibly!
A list of dates down column A (starting at A30) and the value
in F10 in the adjacent cell in column B on that particular date.

Is this possible?
Many thanks

 

[JE McGimpsey]

One way:

If you're not familiar with macros, see

http://www.mvps.org/dmcritchie/excel/getstarted.htm


Put this in the Worksheet code module (right-click the worksheet tab and
choose View Code):

Private Sub Worksheet_Calculate()
Dim rCell As Range
If IsEmpty(Range("B30").Value) Then
Set rCell = Range("B30")
rCell.Value = Range("F10").Value
Else
Set rCell = Range("B" & Rows.Count).End(xlUp)
If Range("F10").Value <> rCell.Value Then _
rCell.Offset(1, 0).Value = Range("F10").Value
End If
End Sub

 

[George Gee]

*JE McGimpsey* has posted this message:


Private Sub Worksheet_Calculate()
Dim rCell As Range
If IsEmpty(Range("B30").Value) Then
Set rCell = Range("B30")
rCell.Value = Range("B28").Value
Else
Set rCell = Range("B" & Rows.Count).End(xlUp)
If Range("B28").Value <> rCell.Value Then _
rCell.Offset(1, 0).Value = Range("B28").Value
End If
End Sub


Many thanks JE, it works brilliantly, I have changed the cell F10 to B28
It helps me see better what is going on!

Could you let me know how to add the cells C30 D30 E30 F30 to this macro?
So that:
B30 updates from B28 (which it now does)
C30 updates from C28
D30 updates from D28
E30 updates from E28
F30 updates from F28

Is this at all possible?
Many thanks for your help.

George Gee

 

[JE McGimpsey]

One way (is this the entire specification?):

Private Sub Worksheet_Calculate()
Dim rCell As Range
Dim i As Long
For i = 2 To 6
If IsEmpty(Cells(30, i).Value) Then
Set rCell = Cells(30, i)
rCell.Value = Cells(28, i).Value
Else
Set rCell = Cells(Rows.Count, i).End(xlUp)
If Cells(28, i).Value <> rCell.Value Then _
rCell.Offset(1, 0).Value = Cells(28, i).Value
End If
Next i
End Sub

 

[George Gee]

JE

It's absolutely spot on!
Treat yourself to a cigar or coconut according to choice!

George Gee



*JE McGimpsey* has posted this message: