Index/Match, Distinct/Collect formula 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
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
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!