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

E_Mitsus

New Member
Joined
Sep 28, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have the foll example table:

Dynamic table.xlsx
ABCDEFG
1Grade23-09-2324-09-2325-09-2326-09-2327-09-23
21
32
43<--base price
54
6
7
8Grade 1= Grade 3 +100
9Grade 2= Grade 3 +50
10Grade = base price
11Grade 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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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:
VBA Code:
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.
1695903622936.png
 
Upvote 1
Solution
this worked perfectly. so perfectly that I wonder why I did ask here earlier. Thank you so much for this reply
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top