Alternative to what/if?? With attachment this time.

[backyardfun]

I attached a file with 2 tabs. The first tab has a list of customer names and their assigned salesperson. The second tab has a dropdown list of the customers in column a. I want column d to automatically fill in the salesperson's name based on the value of column c (which is the result of the dropdown list). Since I have hundreds of customers and always adding more, the if/then would be may to complex. Any suggestions? Thanks!

[mcmahobt]

You can use a number of formulas to achieve this. For this application, I went with INDEX/MATCH

Paste into cell D2 and drag down:

Please Login or Register  to view this content.

Also, using data validation, it wouldn't be necessary to have a separate dropdown in cell A2 and the results in C2. You could just have a dropdown that you reference all in one.

Hope this helps!

[Glenn Kennedy]

I'd use a dynamic named raneg that will add-on new customers and salesmen to the dropdown list (CTRL-F3 to see the set-up); a simple dropdown to select the customer and INDEX-MATCH to pull over the salesman.

Add on a new customer/salesman combo and it all hangs together.

[backyardfun]

That is awesome and will work perfectly. However, how did you make column A a dropdown list like that in a specific cell and define its range? Mine is different?? I add an object (dropdown list). Thanks for your help.

[Glenn Kennedy]

Duplicate post... sorry!!

[Glenn Kennedy]

Select any of the pale green cells in Col A. Go to Data/data Validation and you'll see that i referred to one of the named ranges (Custo) to set the acceptable content for the cells. The really handy part is the formula used to set the named range (check CTRL-F3). This allows for the data validation range to grow as the lsit of names grows, without you having to do anything extra.

If all's well, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks. If not, please shout...

[backyardfun]

thank you so much!!!

[Glenn Kennedy]

You're welcome.... and Thanks!