Repeat cell until Value of 0 occurs again

jaymeebiviano

Good morning,

1. Looking for a formula to do the following: I would like to return "ASSY-00572" in "Column16" for all rows up until "0" occurs again in column "level"
2. Then I would like a formula in column "ASSY TOTAL" that sums the "EXTENDED" column for all rows that list "ASSY-00572" in "Column16"

iLoveData

Do you need this for just ASSY-00572 or dynamic for all variations of ASSY-#### throughout the duration of the sheet?

iLoveData

Do you need this for just ASSY-00572 or dynamic for all variations of ASSY-#### throughout the duration of the sheet?

jaymeebiviano

@iLoveData

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!

iLoveData

@jaymeebiviano

Best I've found is to hard-code 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!

iLoveData

@jaymeebiviano

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

iLoveData