Cross Sheet Formula to list children in separate cells
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
-
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
-
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
Categories
Check out the Formula Handbook template!