Using Closing Stock Balance as Opening Stock in subsequent line item

Question

ITEM	OPEN BAL	IN	OUT	CLOS BAL
A	10	10	0	20
B	200	200	300	100
C	50	100	100	50
D	20	20	40	0
E	100	0	50	50
B
D
E
A
C

In the above Table CLOS BAL is = (OPEN BAL + IN - OUT) for each item. But when the Item appears again below then the OPEN BAL should be the CLOS BAL for that Item. How can I insert the same.

I did the counting about the number of times each item appears and put an Array to identify the item to the corresponding closing balance but could not include the same in the opening balance column.

Answer 1

You can use this formula:

=LET(items,$A$2:$A$13,
closBalance,$E$2:$E$13,
IFNA(XLOOKUP(INDEX(items,ROW()-1),TAKE(items,ROW()-2),
                                  TAKE(closBalance,ROW()-2),,0,-1),0))

XLOOKUP looks only in the above rows and returns the last value (-1)

Be aware that you add the formulas only from row 7. Problems occur when you add a new item which has a fix Opening Balance - then you have to delete the formula.