Filter dropdown list that has multiple identical entries.

**mhlangensiepen** · 08-06-2012, 12:05 AM

Hi,

I have 2 columns in an Excel sheet. Column 1 is a range of colours (e.g. White, Black, Red) and Column 2 is a range of finishes (Matt, Semi, Gloss). The pronlem is that the sheet might have 3 or four White options with a different finish e.g. white matt, white gloss, white semi, white texture

I'd like to use a dropdown list to first select a colour then only be presented with the finishes I require. Is there a way to limit the dropdown list to 1 of each colour then be presented with the finishes for that colour? e.g. first choose white, then choose gloss, ,matt etc. The problem is that the range of finishes varies per colour, some colours might only be available in gloss, others in 2, 3 or more finishes.

Thanks

Mike

**Ace_XL** · 08-06-2012, 03:19 AM

these will help you get started

http://www.excelforum.com/excel-gene...html?p=2881892
http://www.excelforum.com/excel-gene...variables.html

**mhlangensiepen** · 08-06-2012, 08:34 AM

Thanks for the suggestion but as far as I can see, this involves making a named range for each variation of colour. I was rather hoping for a more universal approach as the range of colours can vary month by month and this would require constant editing of the sheet.

**Ace_XL** · 08-06-2012, 09:01 AM

How about one dynamic name range that is based on the selection of the colour? see attached.

**mhlangensiepen** · 08-07-2012, 01:06 AM

Thanks for the suggestion but unless I'm mistaken (quite possible), you have missed the point in your example.

Please have a look at the attached

My first dropdown would have to show ONLY Alabaster, Black and Grey and the second dropdown would then filter the options to what was available only for the colour selected. e.g. Alabaster would then give me Shiny, Matt, Texture, Woodgrain whereas Black would only give me Shiny.

This list is updated about every month so the solution has to be able to take account of a substantial and varying list of colours and finishes. The list is sent to us as an Excel attachment to an email.

Apologies if I wasn't clear in my first post

**Ace_XL** · 08-07-2012, 02:26 AM

You could achieve the list of colours by using a simple pivot table.

You could then create lists pertaining to each colour to look as they did in the sample in Post#4 . This thread might help you achieve that

http://www.excelforum.com/excel-gene...kup-value.html