Cascading drop-down lists

manc

Active Member
Joined
Feb 26, 2010
Messages
340
Good morning,

Having difficulty getting my head around what i am doing wrong.

H!M3 = data validation list called "Consignee"
In Name Manager, "Consignee" = OFFSET(Consignee!$A$2,0,0,COUNTA(Consignee!$A:$A)-1,1)
Consignee!$A:$A contains list of consignee short codes, for example A2=MZOUK_AD, A3=MZOUK_IFG

H!H7 = data validation list called "MZOUK_LOOKUP"
In Name Manager, "MZOUK_LOOKUP" = OFFSET(INDIRECT(H!$M3),0,0,COUNTA(INDIRECT(H!$M3&"col")),1)

I have separate worksheets called "MZOUK_AD", "MZOUK_IFG"

In Name Manager "MZOUK_ADcol" = MZOUK_AD!SA$10:$A$100
This defined range is the same for MZOUK_IFG as well

What should be happening is when a user selects the consignee from a drop-down in M3, they can then go to the drop-down in cell H7 and see the relevant products associated to that consignee.

I have it working on another workbook where the source data for H7 is all on one worksheet, with separate columns for MZOUK_AD & MZOUK_IFG (hence the "col" in the above), but now i need the source data on separate sheets, as it has prices, weights etc that are unique to each consignee regardless if the product is the same.

Where am i going wrong?

In Name Manager, my MZOUK_ADcol and MZOUK_IFGcol are showing the correct values, the problem i think is with the MZOUK_LOOKUP formula.

Any help, greatly appreciated.

Best regards
manc
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Don't think i explained this very well.

Basically, the source data for the cascading drop-down list is on many worksheets.
For example, user selects customerC from first drop-down, then the second drop-down should populate with ColumnA results from worksheet 'customerC'.
It's the second drop-down i'm having difficulty with and how to reference separate sheets as the source data

Any help greatly appreciated.

Best regards
manc
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top