Two lists with a link

[theta]

Hi

I have two lists, linked by a relationship

I want my PARTNER dropdown to only show those client that it is linked with e.g.

Named range 'Clients'
CLIENT1
CLIENT2
CLIENT3
CLIENT4

Named range 'Partners'
PARTNER1
PARTNER2
PARTNER3
PARTNER4
PARTNER5

Links table (VLOOKUP to this?)
CLIENT1 | PARTNER1
CLIENT1 | PARTNER2
CLIENT1 | PARTNER5
CLIENT1 | PARTNER6


Dropdown for partners should =
PARTNER1
PARTNER2
PARTNER5


I need it so that if a customer selects CLIENT1 from a data validation dropdown, the second dropdown will only show those valid Partners the are linked with, and that are available in the partners list (in this example, there is a relationship with PARTNER6, but this partner is not on the partners list so should not appear in the dropdown)

Thoughts?

 

[VoG]

Maybe like this? http://www.contextures.com/xlDataVal02.html

 

[theta]

That would be simple. But mine is a bit more involved.

I have the two static lists, that do not change - Client and Partners.

It is the definitions that change.

So the 'available' partners can increase or decrease.

So effectively want to be able to use the master 'Partner' named range and have it so that only certain items appear from it if they have a link with the (already selected) client.

Am happy to use a helper column or something? Effectively a UNIQUE distinct list but using a second match to take the CLIENT into account?

 

[theta]

Fixed it!

The final IFERROR(MATCH is a bit dirty but gets around the #N/A error when there is no match

Thoughts or ways to improve this guys?

http://www.sendspace.com/file/hkp9wf

Can be downloaded here, have to select the download link (red arrow) next to "LOOKUP WITH FILTER.xlsx"