Index/Match, Distinct/Collect formula question

twarner

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

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

@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

Paul Newcome

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

@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

twarner

@Paul Newcome - found my answer in another one of your answers to a different post:

Paul Newcome ✭✭✭✭✭✭

01/09/20 Answer ✓

To reference a Sheet Summary field, you enter the field name using the same rules as regular column labels (square brackets for spaces, numbers, etc) and use # in place of the row number.

This is the final version of my formula that works just the way I wanted it to:

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

Thank you so much!

Tony

Paul Newcome

Happy to help. 👍️