Drop down list dependent on the result from another.

Hi,

I have tried solving this myself for a couple days now, tried tables, pivot tables, range names, unique lists... just can't work it out.

In the attached sample, I would like a unique drop down list in 'Employees!C2' based in the data 'Directory!A:A.
I did achieve this by adding column 'Directory!D:D' with formula copied down:

{=IFERROR(INDEX('Sample.xlsm'!Area, MATCH(0,COUNTIF($A$1:D1, 'Sample.xlsm'!Area), 0)),"")}

What i required next and where the problem lies is in 'Employees!F2', I need a drop down list from all the store codes in 'Directory!B:B' that correspond to the number selected in 'Employees!C2' that matches 'Directory!A:A'.

A lot of data has been removed for security reasons but this will then later be used to reference the store name in 'Directory!C:C' that matches the store code selected in 'Employees!F2'

I trust all that makes sense lol.

Tez

Works great, Thanks!!

I knew there would be tables involved but just couldn't figure out how to put it all together.

Looked at many website that just confused me

Thanks

Here is a solution that doesn't use tables or VBA.
realize it is a little late but give it a try
Column E is a list of the area codes. Column G is a named variable "selected".
the store codes for an area are listed by the following formula in G2 and copied down.

IFERROR(INDEX(B$2:B$999,AGGREGATE(15,6,(ROW(Area)-ROW($A$2)+1)/(Area=Employees!$C$2),ROWS(G$2:G2))),"")
On the employee sheet the are code drop down source is column E.
The store cod drop down list source is the "selected list.
This should work if the are codes are not grouped or not is sequence.

It is all Purple Smoke & Mirrors.

Thanks

Its always good to see how things work and see other ways of getting results.