STACKUPSERT¶
The STACKUPSERT function is like a combination of STACKADD and STACKFIND, i.e. it can add and update matching cell values from a given SourceRange. Like STACKADD cell values are copied from SourceRange to a specified StackRange if they are not already contained, otherwise they are updated. Anaolog to STACKFIND, a CriteriaRange is used to determine the cell values to copy or update. Updating is done either by replacing the cell value or by using a simple text based arithmetic rule, like “+1” or by specifing an arbitrary formula.
Syntax
=STACKUPSERT(StackRange, SourceRange, CriteriaRange [, AddIfNotFound, Direction, Unique, TargetRange])
Arguments
Name 
Description 

StackRange 
Cell range, to copy SourceRange cells to or to update matching cells. The first row of the StackRange always contains the cell labels and remains unchanged.

SourceRange 
Cell range, where the source data or update rules are taken from. Analog to StackRange the first row contains the cell labels and remains unchanged.

CriteriaRange 
The CriteriaRange has at least 2 rows, the first one is the labels row with labels also found in StackRange and SourceRange (not necessarily all and maybe not in the same order!), the second to nth row contain filter settings.
Settings in the same row are combined using AND, multiple rows are OR.

AddIfNotFound 
Optional. If a matching source row is not found in StackRange it will be added. Specify FALSE to prevent this behaviour.
Defaults to TRUE.

Direction 
Optional. If you set Direction to TRUE the new data is added behind the last empty row on the StackRange.
If the StackRange is full, the first row (after the columns row) is dropped from the stack and all rows move up to make room for the new data in the last row. Analog, if Direction is set to FALSE rows are added at top of the stack and dropped from the bottom.
By default Direction is TRUE.

Unique 
Optional. If set to TRUE equal rows are updated or added only once to StackRange. Two rows are equal if they have same value in each column.
Unique defaults to FALSE.

TargetRange 
Optional. If TargetRange is specified and if a row has to be dropped, the row is copied to the TargetRange.
TargetRange must have two rows, the first row has labels also found in StackRange (not necessarily all and maybe not in the same order!). The dropped values are copied to the matching columns in the TargetRange.
If no row is dropped, the values in the second row of TargetRange are blanked out.
Important: Before copying to the TargetRange all existing values in TargetRange are deleted (not the labels row!)

Update Rules
Function 
Description 

+ 
Add specified number to current value in matching StackRange cell

 
Subtract specified number from current value in matching StackRange cell

= 
Specifies initial value of matching StackRange cell. Applied only on first add.

formula 
its also possible to use any sheet formula to update matching StackRange cell.
Note: if formula references cells from SourceRange which uses text arithmetic it is fully ok if formula returns an error value.

Return Value
TRUE, if no error occurred, otherwise an error value.
Example Below examples assumes following SourceRange:
A 
B 
C 
D 
E 


4 
ItemNumber 
Quantity 
Price 
VAT 
Total 
5 
1234 
+1 
=23 
0.19 
=B5 * C5 * (1 + D5) 
Function 
Result 

=STACKUPSERT(A1:E3,A5:E5,A4:A5) 
After calling STACKUPSERT the first time the row in A1 will contains following values:
1234, 1, 23, 0.19, 27.37
i.e. Quantity is initialized with 1 and Price with 23 and the Total value is 27.37 which was calculated by specified formula.

=STACKUPSERT(A1:E3,A5:E5,A4:A5) 
Calling STACKUPSERT again will update the row in A1 to following values:
1234, 2, 23, 0.19, 54.74
i.e. +1 is used to update Quantity. The Price remains unchanged since it already exists. And again the Total value is calculated by a formula and results to 54.74.
