Index/Match, Distinct/Collect formula question
Hi folks!
I'm trying to create a list of unique values from a crosssheet 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

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.

@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
Answers

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.

@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  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

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!