Question

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

Paul Newcome · Accepted Answer

If you have the users enter the PID into a sheet summary field, you can reference the sheet summary field in the formula and it will still be a valid column formula.

twarner · Accepted Answer

@Paul Newcome Thank you! That sounds like a great idea.  Unfortunately, I can't find how to reference a Sheet Summary field value from a formula in the Sheet. Can you help me out with that?

Thanks,

Tony