Data Validation source too long!

[manc]

Good morning,

My data validation source is too long and i still have more to enter.
I am 100% sure there is a better way of doing this, so please take a look:

=IF($L$3=Consignee!$A$2,MDZ_AD,IF($L$3=Consignee!$A$3,MDZ_KFF,IF($L$3=Consignee!$A$4,MDZ_OSBJ,IF($L$3=Consignee!$A$5,MDZ_OSBD,IF($L$3=Consignee!$A$6,MDZ_SMD,IF($L$3=Consignee!$A$7,MDZ_SMJ,IF($L$3=Consignee!$A$8,MDZ_SAT)))))))

User chooses a consignee from H!L3.
When the selection from H!L3 matches a result in column A on Consignee!, the data validation then populates the drop-down from the named range MDZ_****.
Whilst the named ranges are all different in name, the criteria is the same:
Using MDZ_AD as example:
=OFFSET(MDZ_AD!$A$10:$A$100,0,0,COUNTA(MDZ_AD!$A:$A)-8,1)

The overall result is a user can select products that are relevant to the consignee.

Is there a way to perhaps not have the named ranges and instead incorporate the formula into the data validation source instead?
I have using INDIRECT in the data validation source, and whilst i can get it to display the correct products, because my list is defined as $A$10:$A$100, it lists the blank rows as well, which is rubbish seeing as a consignee may only have one product!

Any help greatly appreciated.

Best regards
manc

 

[RoryA]

I’d suggest either using EVALUATE in another defined name (the same way you would use INDIRECT, but it works with dynamic ranges) or use Tables for your lists and then you can use INDIRECT to refer to them.

 

[manc]

Thanks for your suggestion RoryA, but after a bit of trial and error, managed to get the following to work:
=OFFSET(INDIRECT("'"&$AK$3&"'!$A$10:$A$100"),0,0,COUNTA(INDIRECT("'"&$AK$3&"'!$A$10:$A$100")),1)

Best regards
manc