Cascading drop-down lists with 2nd tier data taken from separate worksheets

[manc]

Good morning,

I'm stuck on this problem.

Two drop-down lists.
One is in A1
The other is in B1

User selects 'customerA' in cell A1,
B1 should then be populated with data from worksheet 'customerA'!$A$2:$A

If user selects 'customerB' in cell A1,
B1 should then be populated with data from worksheet 'customerB'!$A$2:$A

And so on and so on.
I can't seem to find examples of this anywhere.
I've done cascading drop-downs before, but data for B1 has always been on one worksheet.

Any help greatly appreciated.
Best regards
manc

 

[Otiose2]

Try this,

In sheet 1, Cell A1 = CustomerA
Cell A2 = CustomerB

Cell C1 = AUTOMOBILE INDUSTRY
Cell C2 = HEALTH INDUSTRY
Cell C3 = CONSULTANCY SERVICES
Cell C4 = TECHNICAL SERVICES

Cell D1 = BEARING
Cell D2 = ROBOTICS
Cell D3 = AUTOMATION
Cell D4 = CUTTING TOOLS

Now in Name Manager Create name for Cell C1:C4 as LIST1 and Cell D1:D4 as LIST2

In sheet 2, Select Cell A1, create drop down list with 'CustomerA' and 'CustomerB' using this =Sheet1!$A$1:$A$2 in data validation
Select Cell A2, create drop down list using this =IF(A1="CustomerA",LIST1,IF(A1="CustomerB",LIST2)) in data validation

Hope this will work for you.

 

[manc]

Hi Otiose2,

Thanks for your suggestion and it does work for me, so for that i appreciate your time taken to explain in detail.

I have a few questions though.

Now in Name Manager Create name for Cell C1:C4 as LIST1 and Cell D1:D4 as LIST2

I've modified this slightly, as my lists could contain upwards of 100 rows, but some might only contain 10, so the formula i used in name manager is:
LIST1 =OFFSET(CustomerA!$A$10:$A$100,0,0,COUNTA(CustomerA!$A:$A)-8,1)
This removes any blank rows there might be and gives the oportunity to add more items.

I can see issues with the following:

Select Cell A2, create drop down list using this =IF(A1="CustomerA",LIST1,IF(A1="CustomerB",LIST2)) in data validation

I have more than two customers - two was used just for the example purpose. If i have 20, or acquire new ones, using the method you describe i would need to change the formula within data validation each time. Take into account also, that i have space for 15 of these drop-down lists per row - that is a lot of formulas to change.

Maybe MATCH would work, i'm not sure. May have to do some digging to see what i can find.

Nevertheless, i am farther down the road than i was yesterday, so for that i appreciate your work Otiose2

Best regards
manc

 

[manc]

Dear Otiose2,

In case your interested, I've got a more efficient formula to replace this:

Select Cell A2, create drop down list using this =IF(A1="CustomerA",LIST1,IF(A1="CustomerB",LIST2)) in data validation

Especially useful when the Customer list keeps growing, you don't have to go into the data validation source and add the customer each time.
Modify this as your data validation source for your drop-down:
=OFFSET(INDIRECT("'"&$AK$3&"'!$A$10:$A$100"),0,0,COUNTA(INDIRECT("'"&$AK$3&"'!$A$10:$A$100")),1)
In my example:
AK3 = displays lookup value of sheet name
A10:A100 = range to display customer "LIST"

Maybe this will come in handy.
Thanks again for your help.

Best regards
manc