Dynamic table that auto-fill cell values once a cell is added or changed

[E_Mitsus]

Hi,

I have the foll example table:

Dynamic table.xlsx
	A	B	C	D	E	F	G
1	Grade	23-09-23	24-09-23	25-09-23	26-09-23	27-09-23
2	1
3	2
4	3						<--base price
5	4
6
7
8	Grade 1= Grade 3 +100
9	Grade 2= Grade 3 +50
10	Grade = base price
11	Grade 4= Grade 3 -150
Sheet1

In this table I want to add a price every day BUT one day I might add price for Grade 1 and want excel to auto fill the cells for other grades (for that day) based on the pre-arranged premiums or discount.
The next day I may add price on Grade 3 so I would like excel to auto-fill the other grades too.


the premius/discounts (also shown in the attached table) are: Grade 3=base price, Grade 1= +100 on bse price, Grade 2=bsae price +50 and Grade 4=baseprice -150

there are 4 scenarios for each date:
1) If I add price to Grade1 in cell B2 I want B3=B2-50, B4=B2-100 and B5=B2-250
2) If I add price to Grade 2 in cell B3 I want B2=B3+50, B4=B3-50 and B5=B3-200
3) If I add price to Grade 3 in cell B4 I want B2=B4+100, B3=B4+50 and B5=B4-150
4) If I add a price to grade 4 in Cell B5 I want B2=B5+250, B3=B5+200 and B4=B5+150


I tried to find something simmilar but couldnt find. any help would be much appreciated.

tks

 

[Joe4]

Welcome to teh Board!

Assuming that the sample you set up for us is accurate, and you will be entering the values in columns B and up in rows 2-5, I have some VBA code that will do what you want automatically.
Right-click on the sheet tab name at the bottom of your screen, select "View Code", and paste this VBA code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Long
    Dim c As Long
    
'   Exit if more than one cell updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   See if change is made to rows 2-5 after column A
    r = Target.Row
    c = Target.Column
    If (r >= 2) And (r <= 5) And (c > 1) Then
        Application.EnableEvents = False
'       See which row was updated
        Select Case r
            Case 2
                Cells(3, c).Value = Target.Value - 50
                Cells(4, c).Value = Target.Value - 100
                Cells(5, c).Value = Target.Value - 250
            Case 3
                Cells(2, c).Value = Target.Value + 50
                Cells(4, c).Value = Target.Value - 50
                Cells(5, c).Value = Target.Value - 200
            Case 4
                Cells(2, c).Value = Target.Value + 100
                Cells(3, c).Value = Target.Value + 50
                Cells(5, c).Value = Target.Value - 150
            Case 5
                Cells(2, c).Value = Target.Value + 250
                Cells(3, c).Value = Target.Value + 200
                Cells(4, c).Value = Target.Value + 150
        End Select
        Application.EnableEvents = True
    End If

End Sub

And here is a proof of concept. I entered in the red 1000 in each column, and it automatically populate the values in blue.

 

[E_Mitsus]

this worked perfectly. so perfectly that I wonder why I did ask here earlier. Thank you so much for this reply

 

[Joe4]

You are welcome.
Glad I was able to help!