Autogeneration of a column values based on other column values

Mounika

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:

1. If "Interests" is "Quality" then "Department" column should produce " GLQ"
2. If "Interests" is "PIM" OR "Micro" or both of these, then "Department" column should produce " GM"
3. If "Interests" is "CSF" OR "Leukemia" OR "Histology" OR any two OR all of these, then "Department" column should produce " GH"
4. If "Interests" is "Tumor", then "Department" column should produce " GTP"
5. 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!

heyjay

=
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.

Mounika

Hi @heyjay

Thank you so much for responding. This formula is returning "Unparseable". Could you please suggest any other alternatives? Thanks again!

heyjay

This one is working on my end.

1. can you please make sure that we have the same column headers (Interests)
2. 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.