make dropdown menu dependent on other dropdown menu values

Hello,
Wondering if it's possible to have a data validation dependent on adjacent cell value and at the same time ignore blank cells?
Meaning, if i choose from the dropdown in cell B3 in this workbook MainWkbk.xlsx an account (ie "HBA") i would then want the adjacent dropdown in D3 to display only the customers pertaining to that account.
Note, the "blank cells" are not really blank they just look blank but there are formulas in them linking to cells in this external workbook SourceInfo.xlsx (you will have to update the cell reference in sheet1 in the previous attached workbook.

very much looking forward to all responses

See if help
the dropdown in cell d3 i use formula indirect
the sheet2 i write VBA

Please Login or Register  to view this content.

Dear all,
Thank you very much for your responses, all responses work great, thanks again

Originally Posted by Ace_XL

Use this as a defined name lets call it 'List'

=INDEX(Sheet1!B:B,MATCH(MAIN!B3,Sheet1!A:A,0)):INDEX(Sheet1!B:B,MAX((Sheet1!A:A=MAIN!B3)*(ROW(Sheet1!A:A))))

Then in Data validation use List. See attached

Dear AceXL,
I noticed that the method you've suggested only works if the data is sorted according to the dependent drop down, if the information is not sorted according to that then the 2nd dropdown menu displays mostly irrelevant info
Is there any way around this? I would need to have the info sorted by column B.

Thanks