help on price increase on product list ?

hi i have attached a quick test copy of a spreadsheet i am trying to make to keep track of products i make and money from each item .
have worked out how to add it all up and total up money from each product . but what im after help on is if i put my prices up on a item over the course of the year how will i be able to keep track of total ?

as if i change £1.30 in say b4 and there was 10 items then i altered the price to £1.55 and made another 5 items .
how can i do it so it doesnt calculate 15 items @£1.55 instead of 10x£1.30 and 5x£1.55 ? if that makes sense ?

any help would be greatfull

I think you would find it more useful if you arranged your products down a column, and then have other columns for unit price, quantity, total etc.

In answer to your question, though, perhaps you could have a product 1a, then 1b, 1c etc. for price increases, so you would show 10 items of product 1 sold at £1.30 and 5 items of product 1a at £1.55.

Hope this helps.

Pete

With tabel in sheet2 for products and prices
and code in sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myrow As Long, myprice As Double
If Target.Row > 4 Then
    If Target.Column > 1 And Target.Column < 11 And Target.Count = 1 Then
        If Target = "" Then Exit Sub
        Cells(3, Target.Column).Value = Cells(3, Target.Column).Value + Target.Value
        myproduct = Cells(4, Target.Column)
        With Sheets("sheet2")
            myprice = Application.VLookup(myproduct, .Range("A:B"), 2, False)
        End With
        Cells(2, Target.Column).Value = Cells(2, Target.Column).Value + (Target.Value * myprice)
    End If
End If
End Sub

Kind regards
Leo

thank you for your help guys and taking the time to answer will have a play about with the layout and see what i come up with thanks again