Cross Sheet Formula to list children in separate cells

Matt Stewart
Matt Stewart ✭✭
edited 11/12/21 in Formulas and Functions

Im creating an agenda sheet template where people can plug in a parent SKU, and then if there is children it auto populates all the children SKU underneath it, each child in its own cell/row.


I can get it working manually adjusting formulas, but was hoping to get a more user friendly version that people can drag the formula down more rows if there is additional children. (i will also include a helper column that states if there is more children or not)


Formula that works manually:

<<$SKU10 = the parent sku they manually input>>

<<Requires me to manually adjust formula in each SKU11, SKU12, etc to keep adding the additional cell to omit from results>>

=INDEX(COLLECT({SKU Range}, {Parent SKU Range}, $SKU10, {Duplicate SKU Range}, "",{SKU Range}, <>SKU11, {SKU Range}, <>SKU12, {SKU Range}, <>SKU13, {SKU Range}, <>SKU14), 1)


Working formula i can not get to work:

=INDEX(COLLECT({SKU Range}, {Parent SKU Range}, $SKU10, {Duplicate SKU Range}, "", {SKU Range}, NOT(CONTAINS(@cell, COLLECT(SKU$11:SKU14))), 1))

Best Answer

  • Matt Stewart
    Matt Stewart ✭✭
    edited 11/12/21 Answer ✓

    never-mind... after more trial and error i think i got it:


    =IFERROR(INDEX(COLLECT({SKU Range}, {Parent SKU Range}, $SKU$10, {Duplicate SKU Range}, "", {SKU Range}, NOT(CONTAINS(@cell, SKU$11:SKU14))), 1), "")


    now i can have anyone drag fill formulas down for the rows to add the number of children needed to view in agenda

Answers

  • Matt Stewart
    Matt Stewart ✭✭
    edited 11/12/21 Answer ✓

    never-mind... after more trial and error i think i got it:


    =IFERROR(INDEX(COLLECT({SKU Range}, {Parent SKU Range}, $SKU$10, {Duplicate SKU Range}, "", {SKU Range}, NOT(CONTAINS(@cell, SKU$11:SKU14))), 1), "")


    now i can have anyone drag fill formulas down for the rows to add the number of children needed to view in agenda

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!