Formula to Select Multiple Dropdown Options using Two-Column Criteria

Options

Hello,

I am looking to be able to auto-populate the pending permits based on the empty date cells where we input our received dates.

I currently have to manually choose what permits are missing, but am hoping there is a formula I can use to show both missing permits. Below is what it currently looks like and after that is what I'd like it to look like using a formula to generate the two permits missing on the row in the same cell.

I may need to add in a third if missing a Special Permit.

Tags:

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    Options

    @dhills922

    =IF([Town Pole]@row <>"","Town Pole Permit")+" "+IF([State Pole]@row <>"","State Pole Permit")+" "+IF(And([Special Permits]@row<>"",[Special Permits]@row<>"N/A"),"Special Permit")

    There is no functionality to use a multi select option in a formula. How ever you can do text.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    Options

    @dhills922

    =IF([Town Pole]@row <>"","Town Pole Permit")+" "+IF([State Pole]@row <>"","State Pole Permit")+" "+IF(And([Special Permits]@row<>"",[Special Permits]@row<>"N/A"),"Special Permit")

    There is no functionality to use a multi select option in a formula. How ever you can do text.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • dhills922
    dhills922
    edited 05/30/24
    Options

    That worked! I tweaked it a bit and got rid of the "AND" where I don't want to count the N/A and the additional Special Permits section. I never would have gotten that one. Thank you!

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    your very welcome. I did the special permits part just in case you wanted to add that into it maybe at a later point.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!