Repeat cell until Value of 0 occurs again

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"

Tags:

Best Answer

Answers

  • iLoveData
    iLoveData ✭✭✭✭
    Answer ✓

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

    Michael - Alternative Delivery Analyst

    Ames Construction

  • jaymeebiviano
    edited 06/12/24

    @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
    iLoveData ✭✭✭✭
    edited 06/12/24

    @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!

    Michael - Alternative Delivery Analyst

    Ames Construction

  • iLoveData
    iLoveData ✭✭✭✭
    edited 06/12/24

    @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

    Michael - Alternative Delivery Analyst

    Ames Construction

  • iLoveData
    iLoveData ✭✭✭✭

    Michael - Alternative Delivery Analyst

    Ames Construction

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!