Autogeneration of a column values based on other column values
Hi all,
I am looking for a formula to generate values in one column based on multiselect dropdown options in another column.
I have a column named "Interests" with a multiselect dropdown options "Quality", "PIM", "Micro", "CSF", "Leukemia", "Histology" and "Tumor".
Now, I have other column "Department" which should be autogenerated based on these dropdowns mentioned above. Here are the following conditions:
- If "Interests" is "Quality" then "Department" column should produce " GLQ"
- If "Interests" is "PIM" OR "Micro" or both of these, then "Department" column should produce " GM"
- If "Interests" is "CSF" OR "Leukemia" OR "Histology" OR any two OR all of these, then "Department" column should produce " GH"
- If "Interests" is "Tumor", then "Department" column should produce " GTP"
- If "Interests" is more than one selection and is not following in any of the above conditions (Example: "Quality" and "Tumor") then "Department column should produce "Multi-specialty"
I have tried using IF formula, but condition 2 and 3 (if both PIM AND Micro are selected, or if both Leukemia and CSF are selected, then department column is generating "Multispecialty") are not working if is multiselected between those options.
Can someone please, please help me with this? Thanks in Advance!
Answers
-
= IF(Interests@row = "", "", IF(AND(Interests@row = "quality", COUNTM(Interests@row) = 1), "GLQ", IF(AND(Interests@row = "Tumor", COUNTM(Interests@row) = 1), "GTP", IF(AND(Interests@row = "pim", COUNTM(Interests@row) = 1), "GM", IF(AND(Interests@row = "micro", COUNTM(Interests@row) = 1), "GM", IF(AND(HAS(Interests@row, "PIM"), HAS(Interests@row, "MICRO"), COUNTM(Interests@row) = 2), "GM", IF(AND(Interests@row = "CSF", COUNTM(Interests@row) = 1), "GH", IF(AND(Interests@row = "Leukemia", COUNTM(Interests@row) = 1), "GH", IF(AND(Interests@row = "Histology", COUNTM(Interests@row) = 1), "GH", IF(AND(HAS(Interests@row, "CSF"), HAS(Interests@row, "Leukemia"), COUNTM(Interests@row) = 2), "GH", IF(AND(HAS(Interests@row, "CSF"), HAS(Interests@row, "Histology"), COUNTM(Interests@row) = 2), "GH", IF(AND(HAS(Interests@row, "Leukemia"), HAS(Interests@row, "Histology"), COUNTM(Interests@row) = 2), "GH", IF(AND(HAS(Interests@row, "Leukemia"), HAS(Interests@row, "Histology"), HAS(Interests@row, "CSF"), COUNTM(Interests@row) = 3), "GH", "Multispecialty")))))))))))))
I used the function
COUNTM
to specify the number of selection in the cell to 1, 2, or 3 otherwise it wil default to multiselect.I know this could be optimize with
OR
function but I'd like to post this one here while thinking of a better formula....
-
Hi @heyjay
Thank you so much for responding. This formula is returning "Unparseable". Could you please suggest any other alternatives? Thanks again!
-
This one is working on my end.
- can you please make sure that we have the same column headers (Interests)
- Lets try to use the formula by chunks to diagnose the issue.
This is only
= IF(Interests@row = "", "", IF(AND(Interests@row = "quality", COUNTM(Interests@row) = 1), "GLQ", IF(AND(Interests@row = "Tumor", COUNTM(Interests@row) = 1), "GTP", "Multispecialty"
Then this only
=IF(AND(Interests@row = "pim", COUNTM(Interests@row) = 1), "GM", IF(AND(Interests@row = "micro", COUNTM(Interests@row) = 1), "GM", IF(AND(HAS(Interests@row, "PIM"), HAS(Interests@row, "MICRO"), COUNTM(Interests@row) = 2), "GM", IF(AND(Interests@row = "CSF", COUNTM(Interests@row) = 1), "GH", IF(AND(Interests@row = "Leukemia", COUNTM(Interests@row) = 1), "GH", IF(AND(Interests@row = "Histology", COUNTM(Interests@row) = 1), "GH", "Multispecialty"
Then this
=IF(AND(Interests@row = "CSF", COUNTM(Interests@row) = 1), "GH", IF(AND(Interests@row = "Leukemia", COUNTM(Interests@row) = 1), "GH", IF(AND(Interests@row = "Histology", COUNTM(Interests@row) = 1), "GH", IF(AND(HAS(Interests@row, "CSF"), HAS(Interests@row, "Leukemia"), COUNTM(Interests@row) = 2), "GH", IF(AND(HAS(Interests@row, "CSF"), HAS(Interests@row, "Histology"), COUNTM(Interests@row) = 2), "GH", IF(AND(HAS(Interests@row, "Leukemia"), HAS(Interests@row, "Histology"), COUNTM(Interests@row) = 2), "GH", IF(AND(HAS(Interests@row, "Leukemia"), HAS(Interests@row, "Histology"), HAS(Interests@row, "CSF"), COUNTM(Interests@row) = 3), "GH", "Multispecialty"
Let me know which one of the three shows an error.
...
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!