If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Changing values daily
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 -- George Gee -- |
#2
|
|||
|
|||
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 In article , "George Gee" wrote: 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 |
#3
|
|||
|
|||
*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 |
#4
|
|||
|
|||
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 In article , "George Gee" wrote: 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 |
#5
|
|||
|
|||
JE
It's absolutely spot on! Treat yourself to a cigar or coconut according to choice! George Gee *JE McGimpsey* has posted this message: 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 In article , "George Gee" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Newbie Question on Extra Space After Numeric Values | Yellowbird | Worksheet Functions | 3 | March 8th, 2004 05:40 PM |
Average only some values | Andreas | Worksheet Functions | 2 | February 18th, 2004 10:56 AM |
finding distinct values from two lists | Frank Kabel | Worksheet Functions | 0 | February 14th, 2004 12:41 AM |
Using Datetime values as Category | Dave Shea | Charts and Charting | 1 | November 29th, 2003 04:58 AM |