Autogeneration of a column values based on other column values

Options
Mounika
Mounika ✭✭✭
edited 05/14/24 in Formulas and Functions

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!

Answers

  • heyjay
    heyjay ✭✭✭✭✭
    edited 05/13/24
    Options
    =
    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
    Mounika ✭✭✭
    Options

    Hi @heyjay

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

  • heyjay
    heyjay ✭✭✭✭✭
    Options

    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.

    ...

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!