If Formula that Autopopulates names

Tricia Banks

Hi! I need help with a formula that would do the following......

If John Smith was a Client Services Director that was assigned to two states (AL and LA), which formula would I use to populate his name into the column adjacent to the state every time either AL or LA was inputted into the "State" column? And would those names need to be options in the dropdown, or would that complicate matters?

Thank you!

Leibel S

That would be your best option.

Leibel S

@Tricia Banks

Sounds like you have another sheet with names of client services directors and a multi value field of states they are assigned to.

You can then use the formula below (replace the bolded ranges with the correct names...

=IFERROR(INDEX(COLLECT({client services directors range},{Assigned states range},HAS(@cell,[State]@row),1),"No assignee")

Tricia Banks

I do not have another sheet. Is that something that I will need to do before I can autopopulate the field?

Leibel S

That would be your best option.

Andrée Starå

Hi @Tricia Banks

To add to Leibel's excellent advice/answer.

You can also add the list of directors in the same sheet if you prefer.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Tricia Banks

Yes, that would be much easier @Andrée Starå

Can you advise me what that formula may look like? Here is what I currently have:

=IF(State:State = "AL", [Client Service Director:Client Service Director] = "John Smith")

Andrée Starå

@Tricia Banks

How many directors will there be?

Tricia Banks

About 25. I was thinking about splitting the formulas up to alleviate the really long formula.

@Andrée Starå

Andrée Starå

@Tricia Banks

You can either use a long IF formula or a VLOOKUP or INDEX/MATCH.

I'd recommend the lookup option because that makes it easier to make changes to the list to check against.

What do you think?

Tricia Banks

@Andrée Starå

Does the VLOOKUP formula require another sheet? What might that formula look like. I have only been using Smartsheet for a couple of months, and everything I have been playing around with is not working.

Andrée Starå

@Tricia Banks

No, it can be used on the same sheet.

It would look something like this if you create a column with the list of directors (Director List) and before it the State (State List) that the director belongs to.

Like this

State List. Director List

AL Mike

LA Sandra

Try something like this formula.

=VLOOKUP(State@row, [State List]:[Director List], 2, false)

Did that work?

Tricia Banks

@Andrée Starå

Sorry...I'm a newbie. This is how it is set up, which I think is how you referenced it above:

So then is this how I plug in my info?

=VLOOKUP(AL@row, State:[Client Service Director], 2, false)

And where would I input the director name? Currently, there are all dropdowns in the Client Service Director column.

Sameer Karkhanis

@Leibel S Thanks for introducing the COLLECT and HAS functions. Just a minor syntax correction in your formula (missing closing bracket for the COLLECT), fixed below, works like a charm

=IFERROR(INDEX(COLLECT({client services directors range}, {Assigned states range}, HAS(@cell,[State]@row)), 1), "No assignee")

@Tricia Banks I would recommend Leibel's formula (use corrected one above)