Cascading drop-down lists

[manc]

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

 

[manc]

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