Data Validation Question

**daviieejay** · 02-08-2013, 12:23 PM

Good morning to all,

I have a question regarding data validation. This is my situation: I have a very extensive horizontal list of values that I would want to appear in a list (possibly a thousand or more). However, under each column heading, there are values (around 20 or so), varying for the different column headers.

The objective is to create two validation lists. The first is to choose the column header while the second allows you to choose a value that is underneath that column header.

The main problem I have encountered is that I would need to provide a name for each column's values, however with the large amount of columns I have, this would not be time efficient, so I'm searching for an alternative plan, whether it is a simple formula or a macro.

Thank you very much in advance,

Dave.

**Palmetto** · 02-09-2013, 07:39 PM

Hello Dave,

Welcome to the forum.

Explore the attached and see if it works for you.

Sheet one contains two drop down lists using Data validation.

Drop down 1: =listheader
Where listheader is a dynamic named range refering to row-2 on sheet2 using this formula
=Sheet2!$A$2:INDEX(Sheet2!$2:$2,1,COUNTA(Sheet2!$2:$2))

Drop Down 2: =OFFSET(Sheet2!$A$3,0,MATCH(A2,listheader,0)-1,INDEX(rowcount,1,MATCH(A2,listheader,0))-1,1)
rowcount is a named range refering to: =OFFSET(listheader,-1,0)

Row 1 of sheet2 contains counts of used rows in the columns. This count is referenced in the validation formula so that variable lists are sized per the number of items.

Dynamic Validation Lists.xlsx

**daviieejay** · 02-11-2013, 10:56 AM

Good morning Palmetto,
Thank you very much for the example. I applied it to my own spreadsheet and it worked like a charm.

I would like to continue building on his model, creating a third list. In the first example, it was looking up the column headers and then it populates a list based on the values under it. Now, let’s say I have another spreadsheet, with those list values as the headers and a new set of values under it (basically new subvalues, one level lower). I wish to create a third drop down list that functions in the same way as the previous example.

If you could help with that, it would be great.

Thank you so much,

Dave

**Palmetto** · 02-22-2013, 09:18 PM

Hello Dave,

Here is a better version of the dependent validation that uses Excel Tables. Only works for Excel 2007+.
Explore and Learn.

Excel Help_Dependent Validation Lists.xlsx

**daviieejay** · 02-25-2013, 02:24 PM

Thank you for the reply! And hope you had a good weekend.

I think I should attach an example because the format of my question and of the data itself is a little different. As you will see, there are multiple projects with components and elements. Basically after picking a project, I would want to pick only the components found in that project, and then the elements associated with that component only for that project. It's a little tricky so I made a much smaller example of my data set that can help you visualize it.

Thank you for your patience and hard work. Very much appreciated!

Dave