Setting formula to match dropdown list in columns (same row, same sheet)

Hello,

I need help please.

Is there a way to set-up a formula so that an item in a column with dropdown list will automatically select an item in another column with a dropdown list?

For example, Column A has 1,2,3 in dropdown and Column B has 4,5,6 in its dropdown list. If 1 is selected in Column A, it will automatically match with 4 in Column B.

Tags:

Answers

  • sharkasits
    sharkasits ✭✭✭✭✭

    @Jing Is it always a one to one match or do you sometimes want the user to be able to select something specific in Column B?

    If it's a one to one match, you can create a formula in Column B that looks at Column A. If there are a lot of choices, I would create a helper lookup table.

    If you want the person to be able to change Column B, you use an automation, but then you need a path for each value you want automatically selected in Column B.

    I hope that makes sense. If either of these works for what you are trying to do and you need help on the details, let me know.

  • Jing
    Jing ✭✭

    Hi @sharkasits,

    Thank you for taking the time to answer my question. Yes, indeed. It's a one to one match. The choices is only maximum of 5. can you please let me know the step by step process how to create the formula?

  • sharkasits
    sharkasits ✭✭✭✭✭

    @Jing If it's only 5, you can write a formula in column B:

    =IF([Column A]@row = <<value 1>>,<<coresponding Column B value 1>>,IF([Column A]@row = <<value 2>>,<<coresponding Column B value 2>>,IF([Column A]@row = <<value 3>>,<<coresponding Column B value 3>>,IF([Column A]@row = <<value 4>>,<<coresponding Column B value 4>>,IF([Column A]@row = <<value 5>>,<<coresponding Column B value 5>>,"")))))


    Just replace everything between each set of <<value>> with your values.

  • Jing
    Jing ✭✭

    Hi @sharkasits,


    Thanks so much for your guidance. I have tried the formula (please see image below) but I am getting a "CIRCULAR" notification.

    Please advise. Thank you!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!