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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 429 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!