Solved: Data Validation in xl 2000

[Zack Barresse]

Hello,

Trying to work on some Data Validation with dependency lists in Excel 2000. I can do this all day long with 2002 and up, yet I'm having trouble with my 2000.

I'll have a named range drop down list in A1. I want A2 to be populated with the named range (list) of what I've chosen from the A1 list. Although, when I input the formula =INDIRECT(A1) in DV/List, it equates to an error. Anybody know about this in xl 2000?

Like I said, I've done this in 2002. I'm hoping that I've just overlooked something here.

Btw, the named range is a dynamic named range, using an INDEX/MATCH combo to go from row 2 to the last row w/ data in it.

[Jacob Hilderbrand]

I don't have 2000 so...

But try using a regular range for the named range like A2:A100 and see if that works. If it does then we can change the named range with VBA and you should be all set.

[Zack Barresse]

Yup, standard ranges work well with the validation. I was trying to do it in VBA - and I believe my syntax was proper. I'll have to wait until tonight to test as it's on the home pc. Thanks Jake.

[Juan Pablo Gonz?lez]

The INDIRECT() approach does work for me in 2000. However, I have a method that doesn't require the definition of a gizzilion names so that INDIRECT() works.

Using an 'Admin' sheet with a layout like this:

{"Countries","USA","Colombia","","";
"USA","California","Washington","Florida","Illinois";
"Colombia","Antioquia","Cundinamarca","Valle","";
"California","LA","San Diego",0,0;"Washington","Seattle","Redmond","","";
"Florida","Miami","Ft Lauderdale","Orlando","";
"Illinois","Chicago","Marion","Carbondale","";
"Antioquia","Medellin","Bello","","";
"Cundinamarca","Bogota","Chia","Suba","";
"Valle","Cali","Buga","",""}

that is, the "name" of each list in column A, and from B to IV the possible options.

First, create 'BigStr' defined as

=REPT("z",255)

Then, we create the "main" list, 'Countries', which refers to:

=Admin!$B$1:INDEX(Admin!$1:$1,MATCH(BigStr,Admin!$1:$1))

Now, I'll use this lists in Sheet2, where column A will have the country, column B the state/department, and column C the city.

So, in Sheet2, select column A, go to Data | Validation, select "List" and put

=Countries

as the source.

Now, select B1 in Sheet2. This steps is implicitly required for the lists to work ok.

The next name will find the row in Admin that contains the list. Define 'ColListNum' as

=MATCH(Sheet2!A1,Admin!$A:$A,0)

(Note the relative and absolute references)

Now, define 'ColList' as

=INDEX(Admin!$1:$65536,ColListNum,0)

this will retrieve the entire row into a range.

Finally, define 'List' as

=INDEX(ColList,2):INDEX(ColList,MATCH(BigStr,ColList))

Those are the names that we need.

Now, still in Sheet2, select columns B and C.

Go to Data | Validation, select "List" and enter

=List

as the source

Now you can add or delete lists (and their options) without having to redefine anything.

[Zack Barresse]

Thanks Juan.

In testing (2002) I just noticed that my validation would result in an error if the name of my range (whichever was used in the validation) did not start with a capital letter. Is this common? I used a named range 'countries', which evaluated to an error; then I used 'Countries' and it worked just fine. Is this my problem do you think?

Btw, it works wonderfully Juan. That is a very nice alternative.

[xluser2007]

The INDIRECT() approach does work for me in 2000. However, I have a method that doesn't require the definition of a gizzilion names so that INDIRECT() works.

Using an 'Admin' sheet with a layout like this:

{"Countries","USA","Colombia","","";
"USA","California","Washington","Florida","Illinois";
"Colombia","Antioquia","Cundinamarca","Valle","";
"California","LA","San Diego",0,0;"Washington","Seattle","Redmond","","";
"Florida","Miami","Ft Lauderdale","Orlando","";
"Illinois","Chicago","Marion","Carbondale","";
"Antioquia","Medellin","Bello","","";
"Cundinamarca","Bogota","Chia","Suba","";
"Valle","Cali","Buga","",""}

that is, the "name" of each list in column A, and from B to IV the possible options.

First, create 'BigStr' defined as

=REPT("z",255)

Then, we create the "main" list, 'Countries', which refers to:

=Admin!$B$1:INDEX(Admin!$1:$1,MATCH(BigStr,Admin!$1:$1))

Now, I'll use this lists in Sheet2, where column A will have the country, column B the state/department, and column C the city.

So, in Sheet2, select column A, go to Data | Validation, select "List" and put

=Countries

as the source.

Now, select B1 in Sheet2. This steps is implicitly required for the lists to work ok.

The next name will find the row in Admin that contains the list. Define 'ColListNum' as

=MATCH(Sheet2!A1,Admin!$A:$A,0)

(Note the relative and absolute references)

Now, define 'ColList' as

=INDEX(Admin!$1:$65536,ColListNum,0)

this will retrieve the entire row into a range.

Finally, define 'List' as

=INDEX(ColList,2):INDEX(ColList,MATCH(BigStr,ColList))

Those are the names that we need.

Now, still in Sheet2, select columns B and C.

Go to Data | Validation, select "List" and enter

=List

as the source

Now you can add or delete lists (and their options) without having to redefine anything.

Hi all,

I have built a dependent data validation spreadsheet, except the more dependencies I have the more named ranges I need to define.

Thus searching for a more optimal solution to this problem (without using VBA), I stumbled into this thread.

I am getting confused by the layout of Juan Pablo's "Admin" Sheet.

Could anyone please explain, or post up if easier, exactly how his workbook is structured for the example given. I would really like to learn this approach.

regards,

[Bob Phillips]

I have constructed Juan Pablo's example as a workbook.

[xluser2007]

I have constructed Juan Pablo's example as a workbook.

Bob,

That was really great, appreciate your efforts.

This method is so cool, you just need a couple of names and you're set to expand across to as many Dependencies as the columns and rows can hold, great stuff.

Just one question, At the moment, they dependencies move in order from A1:C1.

What if I want the order to be a bit irregular as in A1, then D4 and E7. Is this type of dependency possible easily with this structure?

regards,

[Bob Phillips]

Not really, because it works by picking up the value in the previous cell. That previous cell doesn't have to be A1, B1,C1, and so on, it can be A1, D4, G7, etc., as long as the sequence is consistent.

I suppose that you could do it by declaring a sequence range that you use in a selection change event to re-create the ColListNum defined name, like this

[xluser2007]

Not really, because it works by picking up the value in the previous cell. That previous cell doesn't have to be A1, B1,C1, and so on, it can be A1, D4, G7, etc., as long as the sequence is consistent.

I suppose that you could do it by declaring a sequence range that you use in a selection change event to re-create the ColListNum defined name, like this

Amazing stuff Bob - well done.

This is related to a UserForm I am building, and this VBA-formula driven approach is ideal.

The formula driven approach limits the number of named ranges to 6 (for the Dependent validation), and the VBA "Worksheet_SelectionChange" and "Worksheet_Change" events give great flexibility - a win-win scenario.

BTW, is [<row>] and [<col>] a shortcut for Active row and Active column respectively?

I'll keep you posted with any further queries.

regards,

[Bob Phillips]

No, [<row>] and [<col>] is the number of rows and columns that the predecessor cell is offset from the current cell. For instance, cell A1 is the predecessor of D4, so when D4 is active, these offsets are -3 and -3. The E7 predecessor cell is D4, its offsets are -3 and -1. These are contained in arrays in the selection change evnt, so it needs a bit of setting up. I suppose that you could calculate it in the event code to remove that setup.

[xluser2007]

No, [<row>] and [<col>] is the number of rows and columns that the predecessor cell is offset from the current cell. For instance, cell A1 is the predecessor of D4, so when D4 is active, these offsets are -3 and -3. The E7 predecessor cell is D4, its offsets are -3 and -1. These are contained in arrays in the selection change evnt, so it needs a bit of setting up. I suppose that you could calculate it in the evnt code to remove that setup.

Thanks for the clarification Bob, makes sense now.

I'll experiment bit more with the code and my form and keep you posted with further queries.

thanks again,

[xluser2007]

Hi Bob,

Hope you and your family had a lovely New Year.

I have been playing with this great dependent data validation code set up that you have shown me.

I have attached a workbook, with a suitably created DV list of Funds and their relevant members (all ficticious foir this example), oin the "Admin" worksheet.

In the worksheet called "DV", I have coloured in a few cells where I would like to enter the validation. The colour of the cells corresponds to the relevant level of dependent data validation i would like to have in that cell based on the colours from the "Admin" worksheet.

To be more explicit, for this example:

Dep1, Dep4 depend on A1
Dep2 depends on Dep1
Dep5 depends on Dep4
Dep6, Dep7 depend on Dep5
Dep8 depends on Dep2
Dep9 depends on Dep4
Dep10 depends on Dep9

I have labelled the relevant named ranges as Dep1-Dep10 (no particular reeason, just that I would like to work with named ranges in my application).

Q: Could you please show how to modify your event code to make the above dependencies occur please.

Q2: Also you mentioned above that the event code requires you to manually set up the arrays that identify the relevant locations of the dependent cells - id it possible for the event code to work these out, as with named ranges these may move around a bit initially?

As you know I sincerely value your input and would be grateful if you could help out with this.

[xluser2007]

Attachment added for previous post.

[xluser2007]

Hi Bob,

I was wondering if you had a chance to have a look at this slight revision to the previously discussed problem?

If you could please help me with setting this dependent data validation up, I would really sincerely appreciate it, as I'm a bit unsure how to generalise your code to adapt for this case with multiple dependencies and using named ranges.

kind regards,

[xluser2007]

Hi VBAXers,

If anyone could please help me with my amended query from post #13 onwards, I would be really grateful.

kind regards

[Bob Phillips]

Hi mate,

I have had a go at it, and modified it to deduce the row and column offsets from the list of DV addresses. So all you need to do is construct the list of all DV cell addresses as I have done.

It would be better to be able to use the range names, but I haven't figured that, yet. I will post back if I work it out.

[xluser2007]

Hi mate,

I have had a go at it, and modified it to deduce the row and column offsets from the list of DV addresses. So all you need to do is construct the list of all DV cell addresses as I have done.

It would be better to be able to use the range names, but I haven't figured that, yet. I will post back if I work it out.

Thanks Bob,

Always appreciate your help .

I tried opening your workbook though, and when I click on A1, it gives me a 'Runtime error 9' Subscript out of range error.

And From Dep5 onwards, the validation lists don't seem to be working - any ideas why this may be so, are you experiencing this as well?

sincere regards,

[Bob Phillips]

Oops, messed up on the Match test.

Change this line

[vba]

If aryIndex > 0 Then
[/vba]

to

[vba]

If aryIndex > 1 Then
[/vba]

It doesn't work after level 5 as you don't have any dependent data for those further levels.

[Bob Phillips]

Here is a version using range names