Dynamic dropdown menus

Hey,

Basically, I'm trying to create a tool, and one part of it is when the user enters a client Id, a dropdown will show only the countries the client is set up within. I have managed to achieve this by pivoting out the report, creating a column of formula for every available country alongside it so I essentially end up with a list of all countries for that site and then used a dynamic range in my validation for the drop down cell. I know there must be a simpler way of creating the dynamic dropdown straight from the report but can't think of any wy to do it. Also, I can't use vba for this part either. Any ideas anyone?

Would there be someway to have an if statement defining in the report if that Is the active id being entered, and then create a dynamic range against only those which show a match?

Have a read through this

http://excelsemipro.com/2011/05/a-dy...list-in-excel/