Creation of 2 different validationlists from 1 dropdownlist

[Sophus]

Hi,

I am trying to get 2 different lists (one list in one cell and another list in another cell) both depending on 1 dropdownlist, not using macros.

When choosing country (C6), I want 1 cell returning a list of sales reps. in that country (C7) and another cell returning a list of the pricegroups in that country (C37).

I have solved the pricegroups issue by using the Indirect-function and named ranges and naming the pricegroups after the country. Works perfectly.

I don't know how to make the sales reps work.

I have enclosed my spreadsheet here:

CCF Test.xlsx

Thanks for any help

[Nels]

Firstly, the sales rep needs another name for sales rep content in each country. Such as "Denmark_List"

And, you need to group the Rep country list together in style similar as the content in "Pricelist".
Such that: all sweden sales put adjacently then name the sales content group as "Sweden_Sales".

Is this helps?

[Sophus]

I get the idea of this, but I am looking for a formula to create the list in C7.

Do I use, offset, if then, lookup or something else in my validation?????

[Nels]

I know there's a formula to return the array to lookup the sales rep list
(you need to click CTRL+SHIFT+ENTER to make it as array formula)

Please Login or Register  to view this content.

However, this formula cannot put into the sales rep list. It just for your reference for returning the list of country sales rep.


What I suggest is, you better prepare a tidy sales rep. list and name each country group as list names.(e.g."Denmark_Sales")
Then enter validation formula in C7 as

Please Login or Register  to view this content.

Please let me know if it solves your case

[Sophus]

Thanks a lot Nels

The INDIRECT-solution does the trick (after translating into local language ).

Lesson learned. Great stuff. Keeps it simple.

[Nels]

Nice it helps

Can you do me a favor to reduce this forum admin's work?
You can marked this thread as SOLVED, and clicking the small star icon low left of my answer to add my reputation.