Creating a filtered dropdown

Hi,

Can anyone help me with a filtered dropdown list for the attached sample section of a workbook.

In the example I have:
A dropdown in the Vendor Name column referring to the vendor table, once selected this also pulls back the vendor code.
Once the Vendor has been selected, the user will need to select a valid ILS number to pull back factory details.

What I'm after is a dropdown for the ILS number that will only display numbers available for the selected vendor.

Considerations:
This will extend to hundreds of rows.
Vendor and factory tables can be restructured but cannot be combined.

Can anyone help with this?

Thanks

Gary

Have you looked at Debra Dalgleish's: "...Create Dependent Lists" ?

http://www.contextures.on.ca/xlDataVal02.html

Thanks Jim, but yes I did.

This falls down because it has seperate lists for the second tier reflecting possible permutations from the first tier. With hundreds of vendors with a min of 1 ILS but some having 2,3,or 4 the grid could be huge and unweildy, not to say it couldn't be done but I'd really like something more elegant and straightforward.

On the sample file attached, if you look at the second table (red) on the 'Vendor' tab you 'll see that each vendor can have more than one factory and ILS number.
The first tier dropdown allows selection from the vendor table (blue). The second tier needs to feed from the red table but only show the records that are valid for the choice in the first selection.

Put it all on one sheet and filter it.  See the "Composite List" sheet in the attachment.

I played around with trying to find a use for my 'custom scroll' code but lost interest.

Not quite what I'm after Jim but thanks for trying.

But now - Cracked it - yeehah.

If it can be streamlined I'm open to suggestions. But here goes:

Created a new combination table that can be updated by code when refreshing the master tables - attached file : 'Vendor' tab cells M1:Q34
Created a named range - ILS_anchor - in M1 to create an anchor point for offset references
Created a dynamic named range - ILS_VenNum - to assess the rows in the table and provide an array to run a MATCH on to find which row the target vendor is in.
For simplicity's sake I defined the width of the table (number of factory options) to 4 in this case. This could have been made dynamic but I felt the added complexity for the sake of removing the blanks in a dropdown list wasn't worth the time cost.

Move to the 'Detail' tab and I've added a Validation dropdown list in cell E19 - the source for this list looks for the target vendor in the range ILS_VenNum and using the offsets selects a range of cells to the right of the Vendor Number column.
Happily it also drags down correctly.

Validation formula:
  =OFFSET(OFFSET(ILS_anchor,MATCH($D19,ILS_VenNum,0)-1,1),,,1,4)

Thanks go to Jim and Debra Dalgeish for the prompts to get me here.