Trying to list distinct values from another sheet with a certain status

hdierkers

I am trying to list out distinct values from a separate sheet that are not marked as canceled. I am using this formula, and I only see a blank cell, even though the source sheet is populated.

=IFERROR(INDEX(DISTINCT(COLLECT({Drug Name}, {Project Status}, @cell <> "Canceled")), [Primary Column]@row ), "")

hdierkers

I ended up seeing another article that suggested adding a helper column to add "" to the column name because I was trying to collect distinct values that had repetitive information; then I created a collect/distinct link to that and it worked!

Adam Murphy

Hi @hdierkers,

I would try using JOIN/COLLECT since you are trying to capture multiple values. Try something like =JOIN(DISTINCT(COLLECT({Drug Name}, {Project Status}, @cell <> "Cancelled")), ",")

Would that work?

hdierkers

I ended up seeing another article that suggested adding a helper column to add "" to the column name because I was trying to collect distinct values that had repetitive information; then I created a collect/distinct link to that and it worked!

Marcela

Hey @hdierkers, I'm glad you figured it out! Thank you for sharing your solution!