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
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