Dynamic data validation lists

**FlashyChap** · 01-12-2015, 10:56 AM

Hi there I am new here as I am taking on a more office based role now.
My 1st excel job is creating a new running order here.

What I want to do is to create various data validation drop down lists on the main running order page that reference data on a different sheet.
Now the issue is that the data sheet contains all the company products on the one sheet. This means that I want to reference a range of cells that are constantly in flux as products are added or taken away from the line.
However each product will have a type attached to it, for example all the stove entries that I want to reference have a column that simply contains the word 'stoves'. Alternatively they have a code no. column that for these would start XS00.....

So basically I'm a bit stuck on how to dynamically alter the cells that the data validation list references from

Thanks in advance

Chris

**mcmahobt** · 01-12-2015, 11:08 AM

You will need to use a named range and reference that within your list. You will want to use a version of this formula:

Please Login or Register  to view this content.

Altered to suite your needs.

I have attached an example workbook so hopefully this becomes more clear since there is no sample workbook attached to play with.

Hope this helps!

**FlashyChap** · 01-12-2015, 11:31 AM

Wow thanks seems to work well.
Messes up a bit if the column containing the data get sorted differently but it is easy to add stuff in the middle or above so should do the trick.

Thanks Mcmahobt for your swift response