Repeat cell until Value of 0 occurs again
Good morning,
 Looking for a formula to do the following: I would like to return "ASSY00572" in "Column16" for all rows up until "0" occurs again in column "level"
 Then I would like a formula in column "ASSY TOTAL" that sums the "EXTENDED" column for all rows that list "ASSY00572" in "Column16"
Best Answer

Do you need this for just ASSY00572 or dynamic for all variations of ASSY#### throughout the duration of the sheet?
Michael  Alternative Delivery Analyst
Answers

Do you need this for just ASSY00572 or dynamic for all variations of ASSY#### throughout the duration of the sheet?
Michael  Alternative Delivery Analyst

The ladder  dynamic for all variations of ASSY#### throughout the sheet. There are thousands of assemblies I am trying to compute cost for! Thanks for asking!

Best I've found is to hardcode in the first Assembly at cell 1  Level 0 within Column 16, then apply this formula to all other rows (2  ###) within column 16 <=IF([LEVEL]@row = 0, [ITEM NUMBER]@row, [COLUMN16]1)>
See Drawing below (Apologies for poor handwriting, I did it with my mouse real quick)
After all the rows are normalized to the Assembly needed, I would create a linked report to view the sums at each assembly. The issue with doing it in the sheet is the sheer scale of it. You would need to do a massive COUNTIF formula that is unrealistic given the number of assemblies. This report will scale based on the sheet and will not need to be updated separately as long as the main sheet is current.
First go to summary and do a SUM of the column holding the assembly values
Then Group by your Column 16 values
You should end up with the following result showing the subtotals and totals at each assembly level at level 0. Hope this helped!
Michael  Alternative Delivery Analyst

Hey There,
Took a few stabs at it and ultimately this is the best I could come up with. First, hard code the first assembly number into the sheet. This should be at level 0 and row 1. After that you can apply the following formula to the rest of the rows within your column16 <IF([LEVEL]@row = 0, [Item Number]@row, [Column16]1)> Make sure this is applied at row 2 and beyond. It will break if it's applied to row 1 due to SmartSheet being unable to error reference a null value at row 0.
Next I would build a report off this sheet and Summarize your Extended Column as a SUM and group by COLUMN16. The end result would look like this
Michael  Alternative Delivery Analyst

Help Article Resources
Categories
Check out the Formula Handbook template!