google-sheetsarray-formulascircular-dependencycircular-reference

Automatic Formula when Inserting a New Row in between in Google Sheet without Circular Dependency

I was trying to automatically add formulas when a new row is inserted in between and I did suceed with using arrayformula but now it gives error of circular dependecy. I do NOT want to use a SCRIPT for this. I tried too many methods like SUMIF, INDIRECT, etc. but it ain't working for me. I have made a DEMO sheet if someone can solve.

https://docs.google.com/spreadsheets/d/1J1Lmz0QrGRUyknTc1GlxwS7p6uJdSojlhEB-HkpcW6M/edit?usp=sharing

I have written the original formula as well as the arrayformula that gives error. Wil be greatly helpful if someone can solve.

Thank you :)

EDIT: This was solved by rockinfreakshow but I have posted a new question for other cells: Google Sheets LAMBDA Blank cells with IF condition

Solution

  • You may try this for Total Qty

    ={"total qty";map(indirect("B3:B"),indirect("E3:E"),lambda(Σ,Δ,if(Σ="",,sumif(B3:Σ,Σ,E3:Δ))))}

    enter image description here