Help with INDIRECT

[Anita Taylor]

I have a list of values and subvalues on a worksheet called DataValues and am
trying to use the INDIRECT function for Data Validation as documented at
http://www.contextures.com/xlDataVal02.html.

I'm trying to create one column on an entry sheet that allows you to select
from List A, then, in the adjacent cell, select a corresponding entry from
List B - the data in List B (Column 2) is dependent upon what was selected in
List A (column 1).

The example at this site shows List A with a choice of either Fruit or
Vegetable, then List B (Fruit List) and List C (Vegetable List) contain
corresponding entries.

I think I have everything defined, but when I get to the point where I
actually try to use the INDIRECT function to validate data in my second
column, I cannot get the correct formula. If I try to enter the worksheet
name and reference, I get an error and cannot get this secondary/dependent
list to populate correctly.

I'd appreciate any help!

 

[JMB]

What are you using for your indirect formula?? Is List A located on the same
worksheet to which you are trying to applying data validation? Data
validation cannot reference a range that is on a different worksheet unless
you use a named range. So something like =INDIRECT(SomeOtherSheet!A1) would
not work, but =INDIRECT(ListA) would.

 

[Herbert Seidenberg]

If you had 5 Data Validation columns
and 10 unique choices for each selection
then you would have to make lists for 10000 items.
In my example I limited the number of choices
so only 50 rows of data need to be entered.
Excel 2003 List and Excel 2007 Table
will give you dependent drop-down lists
without formulas:
http://www.freefilehosting.net/download/3d4f5

 

[Anita Taylor]

I have named each range - the problem is that I cannot get the dependency to
work. I have one list for the values I want to select for A1. I want B1 to
present another list of values based on which selection was made in A1.
That's where I'm getting stuck.

Normally, I would do this in Access, but am working with a group more
comfortable with Excel. I've never tried anything like this in Excel and am
not even sure Excel can do something like it.

And, the value lists are all in a separate worksheet, but in the same
workbook.

 

[Anita Taylor]

I took a look at this, but I'm not sure how to use it. I apologize in advance
for my lack of knowledge in more advanced functions of Excel - I'm more
familiar with other Office products. I'm not even sure if Excel CAN do what I
want - just from looking at on-line help, I thought it might. I just can't
seem to get it to work for me.

 

[JMB]

If you have ListA and ListB (both named ranges) on Sheet1. Then on Sheet2
cell A1 you click Data/Validation,
Allow: List
Source: ListA, ListB
Click OK

then select cell B1 and click Data/Validation
Allow: List
Source: =INDIRECT(A1)

Everything seems to work fine for me. Try to include more details so that
we can duplicate *exactly* what you are doing. What happens when you enter
the indirect function into the data validation box? Error message? Did you
remember the = sign? Are your named ranges declared globally or locally?