Aug 14 2021 06:08 PM
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.
Aug 14 2021 09:20 PM
@Mailutoronto Why not build one table with beginning quantities and all subsequent changes and then summarize that one with a pivot table? Example attached.
Aug 14 2021 10:19 PM - edited Aug 14 2021 10:22 PM
SolutionOne 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
Aug 15 2021 01:27 AM
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))
Aug 15 2021 08:26 AM
Aug 17 2021 03:47 PM
Aug 17 2021 03:49 PM
Aug 17 2021 04:06 PM
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.
Aug 17 2021 04:49 PM
Aug 18 2021 04:59 AM
Sadly some of my naming was not helpful. In the following
FILTER(stockQty, stock>0)
the first term was a two column array of tool name and its total quantity. The second was the stock quantity. What FILTER does is retain the rows that satisfy the condition
stock > 0
so giving a fully dynamic result that changes as the data is revised. I used LET simply because that is the way I program Excel. In this instance it would be pretty easy to do without by nesting the variable definitions within the final formula.
= FILTER(
CHOOSE(
{1,2},
UNIQUE(Change[Tools]),
SUMIFS(Change[Qty], Change[Tools], tool)),
SUMIFS(Change[Qty], Change[Tools], tool)>0)
)
Aug 23 2021 07:38 PM
Aug 23 2021 07:56 PM
Aug 24 2021 02:28 AM
You are not alone. It happens frequently that the Excel 365 formulas I publish are not even recognised as spreadsheet formulas! I have tidied up the naming a little to make the quantities a little clearer.
= LET(
tool, UNIQUE(Change[Tool]),
stockQty, SUMIFS(Change[Qty], Change[Tool], tool),
stockTbl, CHOOSE({1,2}, tool, stockQty),
FILTER(stockTbl, stockQty>0))
The formula that generates the table is in cell F3. A fairly normal trick for debugging a LET formula, is to return one of the local names at a time as the result of the calculation
= LET(
tool, UNIQUE(Change[Tool]),
stockQty, SUMIFS(Change[Qty], Change[Tool], tool),
stockTbl, CHOOSE({1,2}, tool, stockQty),
result, FILTER(stockTbl, stockQty>0),
stockTbl)
By inspecting each name in turn, one is able to gain insight and debug the formula. So far, this only applies to Excel 365, but it represents the most profound change to occur in spreadsheet technology since they were first introduced in the 1980s.
This may represent the emergent butterfly, but many will continue to admire and play only with the caterpillars!
Aug 14 2021 10:19 PM - edited Aug 14 2021 10:22 PM
SolutionOne 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