Validations Based On Previous Validations

**PhatPhil** · 11-12-2009, 07:09 AM

I am New to the Forum and I am more an MSAccess programmer who is now delving into Excel.

On a worksheet I want 4 cells to have validation, when a selection in the first cell is made this limits the choice in the second, when the second is selected then the third is limited by 1 and 2 and the forth will be limited by 1,2, and 3.

In the attached example data , if I choose a Region, I have a choice of Countries.
When I choose a Country I am limited to valid Cities and when I have chosen a City then a list of applicable Agents will be available.

I have used NamedRanges for a two dimensional lookup but for four and my list will be far bigger than the sample, I do not fancy maintaining numerous NamedRanges.

Is there a way of doing what I want to do.

**teylyn** · 11-12-2009, 07:18 AM

Hi,

this is called dependent data validation. For a tutorial see

http://www.contextures.com/xlDataVal02.html

hth

**PhatPhil** · 11-12-2009, 08:19 AM

Teylyn, thanks for your quick reply.

I have seen similar work as the example linked on your reply.

Looking at it, it appears to be just another way by which you don't have to do NamedRanges. The number of Ranges you would have to create would be the same. Its looks simple because the example is only two columns, I am working on data which will be 4 columns of data, with 12 options in the first column, each 12 has about 8 options, these 8 have 6 options and the 6 options have about another 6 options.

I have caculated I would have to define circa 288 ranges. I imagine development/maintainence would be a nightmare.

In MSAccess ( I know this Excel but I am being forced to use it) I would create one table and in a form this first validation would OnChange set second's source data via a query and the process would follow through the 3rd and 4th.

I was hoping a similar process was available in Excel whether it be via MSQuery or ADO for example or can I use an Excel form to emulate what MSAccess is capable of.

**teylyn** · 11-12-2009, 02:56 PM

Well, AFAIK the dependent data validation is how it works in Excel, and it works best when the ranges are pre-defined and named. There has just been a thread investigating why the technique does not appear to be working when the ranges are dynamically defined with OFFSET and the range names fed into INDIRECT are concatenated based on other input. There seems to be a limit of what the INDIRECT function can do when it sits in the data validation reference. Here's the thread

http://www.excelforum.com/excel-gene...ed-ranges.html

Maybe someone will come up with a solution, but I doubt it.

cheers

**PhatPhil** · 11-16-2009, 04:05 AM

My apologies to Teylyn, my bias towards

MSAccess and my lack of understanding of what he was telling me.

With a what Teylyn suggested in his post and a little more research, I now understand what is required and have solved my particular issue.

I have attached my solution, I hope other members find it useful.

Note the use of Named Ranges.