Index/Match, Distinct/Collect formula question

twarner
twarner ✭✭✭✭
edited 02/15/24 in Formulas and Functions

Hi folks!

I'm trying to create a list of unique values from a cross-sheet column based on a single criteria.

This is a tool for users, and I want them to be able to provide the input for what the value of that single criteria is.

What I have that works is:

=INDEX(DISTINCT(COLLECT({Application Estimates by PID: MOTS ID}, {Application Estimates by PID: PRISM ID}, PID@row)), [Row #]@row)

This works, but requires the user to copy the criteria value ("PID") to a bunch of rows, which is definitely not ideal.

I thought just changing the formula to reference just "PID1" would work:

=INDEX(DISTINCT(COLLECT({Application Estimates by PID: MOTS ID}, {Application Estimates by PID: PRISM ID}, PID1)), [Row #]@row)

BUT, that actually gives me the "The column formula syntax isn't quite right..." ERROR.

I want the user to just be able to open this sheet, type in their PID ONCE, and get their list of unique App IDs for their Project. What do I need to do differently?

Thanks,

Tony

Best Answers

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!