Solved: Data Validation in xl 2000

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.

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.

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. :yes

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.

Thanks Juan. :yes

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. :)

Quote:

---

Originally Posted by 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.

---

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,

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

Quote:

---

Originally Posted by xld

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,

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

Quote:

---

Originally Posted by xld

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,

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.

Quote:

---

Originally Posted by xld

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,

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.

Attachment added for previous post.

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,

Hi VBAXers,

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

kind regards

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.

Quote:

---

Originally Posted by xld

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,

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.

Here is a version using range names

Quote:

---

Originally Posted by xld

Here is a version using range names

---

Hi Bob,

My apologies for not replying sooner. I just got a new computer yesterday, so true to my avatar, I have upgraded my computer to a Vista and am now an Excel 2007 user. From your previous posts, I know Excel07 has advantages (the IFERROR functions etc and the fact that analysis toolpak is built in), and not so great features (such as removing the F4 key and the Ribbon format). Should eb interesting to experiment with.

Thank you sincerely for your help with this problem. I am going to try it out on my Userform and will let you know if I have any queries.

regards,

2007 is an interseting journey (one way to put it). I must admit that I only use it when I have a job that needs it, otherwise I still use 2003.

As for Vista, my wife has it, but I have only ever used it on her laptop. With Windows 7 in beta now, I will be passing Vista by.

Did you get it sorted it mate?

Quote:

---

Originally Posted by xld

Did you get it sorted it mate?

---

Bob,

Thanks for your follow up. Thanks to your method, I did get it sorted :friends:.

I've had a few internet connection issues this weekend, so couldn't reply to you sooner.

BTW, Excel 2007 is good for beginners, but for those people that use Excel 2003 on a day to day basis, it is a bit tedious to figure out simple things like how to unhide sheets in it. I am still trying to get used to it, there must be some good stuff there. Do you know if the next generation of Office will revert back to the Excel 2003 menu structure from the Ribbon format?

As for you passing on Vista, it seems like a good idea to wait out for the Next Windows. I must admit though, it certainly is quite a pleasant looking operating System (for my simple home desktop use), and am finding that the main issue is the multiple "Continue" or "Close" Selections you have to keep clicking.

regards,

Glad it is all okay now.

2007 might be good for beginners, but my main objection is that MS seemed determined that they know best, that they could/should dictate to us how we should work. It is harder to push Excel as we could (easily) with 2003, it is slower to use, and is generally a less useful product. They seem to believe that all Office products are much of a muchness and should work the same - they aren't, I could easily live without Outlook, Powerpoint or Access, but not Excel (2003). Ally that to the fact that they still determined to push out VBA and drive Office programmability to .Net which is a lot tougher for the business types, and you have a misguided policy IMO. MS should stop being obsessed by prettying the product, and dictating how we work, and look to improve many of the functional improvements that are desperately required

Excel has become so ubiquitous because it did its basics well, but it was easily extendible so that the market could drive the way it deveopled, not some hidebound bureacratic corporation. If they destroy that, they are playing with fire, if OOO improves it will grab huge market share.

Similarly, Vista may be pretty, but that is not the (primary) aim of an OS. It should make life easy to get at the real stuff, networking, files, applications. Vista fails in that IMO, and it is huge, needing ever more hardware. Netbooks are starting to take real market share - MS be warned.