Index Distinct Collect
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?
Best Answer

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)), ", ")
Answers

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)), ", ")
Help Article Resources
Categories
Check out the Formula Handbook template!