I have two sheets, one which has the latest price of an item and another
which maintains a history of the price per item, I need to be able to update
the historical price on sheet 2 in the correct column when the price is
changed on sheet1.

Example

Sheet1

Item Price
A 10
B 12


Sheet2

Item Jan Feb Mar Apr...
A 8 9 8 10
B 12 11 10 12

The values in sheet2 for April need to be updated by the changes to the
values in sheet1

The column offset is particularly perplexing me!

Anyone got any ideas

Many thanks in advance

right click on the sheet tab of Sheet1 and select view code. Put in code
like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nMth As Long, sVal As String
Dim rng As Range, rng1 As Range
Dim res As Variant
If Target.Count 1 Then Exit Sub
If Target.Column < 2 Then Exit Sub
nMth = Month(Date)
sVal = Cells(Target.Row, 1)
With Worksheets("sheet2")
Set rng = .Range(.Cells(2, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
res = Application.Match(sVal, rng, 0)
If Not IsError(res) Then
Set rng1 = rng(res)
rng1.Offset(0, nMth).Value = Target.Value
End If

End Sub

--
Regards,
Tom Ogilvy

"Steve Barber" wrote in message
...
I have two sheets, one which has the latest price of an item and another
which maintains a history of the price per item, I need to be able to
update
the historical price on sheet 2 in the correct column when the price is
changed on sheet1.

Example

Sheet1

Item Price
A 10
B 12


Sheet2

Item Jan Feb Mar Apr...
A 8 9 8 10
B 12 11 10 12

The values in sheet2 for April need to be updated by the changes to the
values in sheet1

The column offset is particularly perplexing me!

Anyone got any ideas

Many thanks in advance

Works a treat - Thank you very much!!