Index Distinct Collect

Jennifer Lindquist

I have a sheet called PN List that has a checkbox column to signify which parts are packaged parts.

I'm creating a new sheet that gives me a list to be used in a dropdown for just packaged parts.

In the new sheet PN List - Pack, I have two columns 1 - PPN (column I want to fill), 2 - PaintHelper (checkbox column for collecting)

In the PN List - Pack, PPN column I have the formula:

=INDEX(DISTINCT(COLLECT({Part Number}, {Ck If Painted}, PaintHelper@row)), 1)

{Part Number} - Part number column on PN List sheet

{Ck If Painted} - Checkbox column on PN List sheet

PaintHelper@row - Checkbox column on PN List - Pack sheet. All the boxes in the rows are checked.

It used to work, now it doesn't. What am I missing?

jmyzk_cloudsmart_jp

Hi @Jennifer Lindquist

Your formula "=INDEX(DISTINCT(COLLECT({Part Number}, {Ck If Painted}, PaintHelper@row)), 1)" worked OK when I tested.

You can get the same result without using the PaintHelper column by changing your formula to the following as the second sheet shows in the summary filed, PPN - One, as the PaintHelper@row is giving 'true' criteria to the {Ck If Painted} range;

=INDEX(DISTINCT(COLLECT({Part Number}, {Ck If Painted}, 1)), 1)

or in my demo dashboard

=INDEX(DISTINCT(COLLECT(PartNumber:PartNumber, [Check If Painted]:[Check If Painted], 1)), 1)

If you want all the distinct Paint Numbers whose Check If Painted columns are checked, you can use the formula in the PPN - ALL Formula field.

=JOIN(DISTINCT(COLLECT(PartNumber:PartNumber, Check:Check, @cell = 1)), ", ")

https://app.smartsheet.com/b/publish?EQBCT=f3a1ed5c302648fdbabc04af85e8b929

jmyzk_cloudsmart_jp

Hi @Jennifer Lindquist

Your formula "=INDEX(DISTINCT(COLLECT({Part Number}, {Ck If Painted}, PaintHelper@row)), 1)" worked OK when I tested.

You can get the same result without using the PaintHelper column by changing your formula to the following as the second sheet shows in the summary filed, PPN - One, as the PaintHelper@row is giving 'true' criteria to the {Ck If Painted} range;

=INDEX(DISTINCT(COLLECT({Part Number}, {Ck If Painted}, 1)), 1)

or in my demo dashboard

=INDEX(DISTINCT(COLLECT(PartNumber:PartNumber, [Check If Painted]:[Check If Painted], 1)), 1)

If you want all the distinct Paint Numbers whose Check If Painted columns are checked, you can use the formula in the PPN - ALL Formula field.

=JOIN(DISTINCT(COLLECT(PartNumber:PartNumber, Check:Check, @cell = 1)), ", ")

https://app.smartsheet.com/b/publish?EQBCT=f3a1ed5c302648fdbabc04af85e8b929