Collect drop-down value used in a column
I am looking for a function to collect drop-down value used in a column, and saved in a cell.
For example, on "Types" column, it has "Type A"-"Type B" as a list, whatever the list value used in each row, I want to store used ones in the first row.
Best Answer
-
Hi Roy,
If I'm understanding you correctly, you have a column with multiple options to select, and this column may have the same option selected more than once. However, you want one summary cell that returns each of the Types selected, once.
Like this?
If so, I you can build an IF(HAS statement for each Type, which would return the name of the Type if it's present in the column. Then you can add all these IF statements together. You'll notice I added + " " to add in a space between the types, as well.
=IF(HAS(Types:Types, "Type A"), "Type A") + " " + IF(HAS(Types:Types, "Type B"), "Type B") + " " + IF(HAS(Types:Types, "Type C"), "Type C")... etc
I input this formula in a column next to it so that I could easily reference the entire column by writing Types:Types. If you want to have this formula in the same column, you will need to adjust the reference to have a start-row and an end-row, such as the following: Types11:Types35
Here are some Help Center articles I used: IF function / Joining Text in Formulas / HAS function / Column References in formulas
Let me know if this works for you!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi Roy,
If I'm understanding you correctly, you have a column with multiple options to select, and this column may have the same option selected more than once. However, you want one summary cell that returns each of the Types selected, once.
Like this?
If so, I you can build an IF(HAS statement for each Type, which would return the name of the Type if it's present in the column. Then you can add all these IF statements together. You'll notice I added + " " to add in a space between the types, as well.
=IF(HAS(Types:Types, "Type A"), "Type A") + " " + IF(HAS(Types:Types, "Type B"), "Type B") + " " + IF(HAS(Types:Types, "Type C"), "Type C")... etc
I input this formula in a column next to it so that I could easily reference the entire column by writing Types:Types. If you want to have this formula in the same column, you will need to adjust the reference to have a start-row and an end-row, such as the following: Types11:Types35
Here are some Help Center articles I used: IF function / Joining Text in Formulas / HAS function / Column References in formulas
Let me know if this works for you!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Are you a genius? 😍😍
Thank you!
-
Haha! You're welcome - glad that it worked for you. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!