Forum Discussion
How do you create table that automatically add or delete rows?
Suppose I have the tools list and its changes tabulated below:
Then in the bottom table I recorded a change of adding 2 bolts. How do you make the tool table automatically add a row in the top table and appear like this:
In reverse, if I used up 5 nails, how do you make the tool table automatically delete a row in the top table and appear like this:
The table that record changes should be the one that increases forever, while the top table will expand or contract depending on the availability of tools.
One way is to use VBA in order to achieve this functionality.
In the attached, you will find two tables named "Main" and "Change" where table "Main' contains some records. And once you start adding records in the Change Table, the Main Table will update accordingly.
The Sheet1 Module Contains the following Change_Event Code in it.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub Dim tblMain As ListObject Dim tblChange As ListObject Dim n As Variant Dim r As ListRow Set tblMain = ActiveSheet.ListObjects("Main") Set tblChange = ActiveSheet.ListObjects("Change") On Error GoTo Skip If Not Intersect(Target, tblChange.DataBodyRange.Cells) Is Nothing Then Application.EnableEvents = False If Target.Column = tblChange.DataBodyRange.Columns(1).Column Then n = Application.Match(Target.Value, tblMain.DataBodyRange.Columns(1), 0) If IsError(n) Then If tblMain.DataBodyRange.Cells(1) = "" Then Set r = tblMain.ListRows.Add(1) Else Set r = tblMain.ListRows.Add End If r.Range(1, 1).Value = Target End If Target.Offset(0, 2).Value = Date Target.Offset(0, 2).NumberFormat = "dd-mmm-yyyy" ElseIf Target.Column = tblChange.DataBodyRange.Columns(2).Column Then n = Application.Match(Target.Offset(0, -1).Value, tblMain.DataBodyRange.Columns(1), 0) If Not IsError(n) Then Set r = tblMain.ListRows(n) r.Range(1, 2).Value = r.Range(1, 2).Value + Target.Value If r.Range(1, 2).Value = 0 Then tblMain.ListRows(n).Delete End If End If Target.Offset(0, 1).Value = Date Target.Offset(0, 1).NumberFormat = "dd-mmm-yyyy" End If End If Skip: Application.EnableEvents = True End Sub
12 Replies
- Without VBA you can use sequence and if with some logical cheat, see my post on aug 23 2021
Excel 365 can achieve similar goals with worksheet formulas.
= LET( tool, UNIQUE(Change[Tools]), stock, SUMIFS(Change[Qty], Change[Tools], tool), stockQty, CHOOSE({1,2}, tool, stock), FILTER(stockQty, stock>0))- thanks for your response. I don't know what it is you did here. Are the codes in VBA/Macro? If so, I think I"ll have to learn it myself since I never used it in Excel before.
Are you able to point me to some sources for learning what it is you did and maybe the topics that can quickly guide me. Or should I learn whatever it is from scratch? Thank you,It is just a regular Excel formula. OK, not so regular since most users will not recognise the formula as Excel! The following announces that LET is now generally available on Excel 365.
LET Generally Available (microsoft.com)
Searches of the Microsoft site should give links to the introduction of Dynamic Arrays (2018), the Announcement of LET and, still on insider beta channels the announcement of Lambda functions and Lambda helper functions that feed array parameters into Lambda functions and collect the results to form an array of results.
It all represents the most dramatic change imaginable.
One way is to use VBA in order to achieve this functionality.
In the attached, you will find two tables named "Main" and "Change" where table "Main' contains some records. And once you start adding records in the Change Table, the Main Table will update accordingly.
The Sheet1 Module Contains the following Change_Event Code in it.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub Dim tblMain As ListObject Dim tblChange As ListObject Dim n As Variant Dim r As ListRow Set tblMain = ActiveSheet.ListObjects("Main") Set tblChange = ActiveSheet.ListObjects("Change") On Error GoTo Skip If Not Intersect(Target, tblChange.DataBodyRange.Cells) Is Nothing Then Application.EnableEvents = False If Target.Column = tblChange.DataBodyRange.Columns(1).Column Then n = Application.Match(Target.Value, tblMain.DataBodyRange.Columns(1), 0) If IsError(n) Then If tblMain.DataBodyRange.Cells(1) = "" Then Set r = tblMain.ListRows.Add(1) Else Set r = tblMain.ListRows.Add End If r.Range(1, 1).Value = Target End If Target.Offset(0, 2).Value = Date Target.Offset(0, 2).NumberFormat = "dd-mmm-yyyy" ElseIf Target.Column = tblChange.DataBodyRange.Columns(2).Column Then n = Application.Match(Target.Offset(0, -1).Value, tblMain.DataBodyRange.Columns(1), 0) If Not IsError(n) Then Set r = tblMain.ListRows(n) r.Range(1, 2).Value = r.Range(1, 2).Value + Target.Value If r.Range(1, 2).Value = 0 Then tblMain.ListRows(n).Delete End If End If Target.Offset(0, 1).Value = Date Target.Offset(0, 1).NumberFormat = "dd-mmm-yyyy" End If End If Skip: Application.EnableEvents = True End Sub- This includes the function that I imagined. However, it does include flows. No need to fix because I think I'll have to learn VBA now and I wasn't exactly expecting this since almost all of my previous sheets were well handled by Excel without VBA/Macros.
The rows auto add and auto deleted based on variables that is quite narrow and leads to some malfunctions. I'll have to include more rules myself.
Can you point me to certain topics in VBA to learn what you did? or do I have to learn everything from scratch?
Mailutoronto Why not build one table with beginning quantities and all subsequent changes and then summarize that one with a pivot table? Example attached.
- Pivot table does help with customizing a summary of the data. However, the reason I specifically wanted to achieve this the way I wanted is because its a small component of a much larger project that I actually want to do: stock portfolio.
The entries will be numerals but less detailed, while the summary will be really detailed but few and change according to the entries. The pivot table, as far as I know, won't be as useful.
