Data Validation Dependant Lists

  • Thread starter Thread starter andyp161
  • Start date Start date
A

andyp161

Hi there,

Is it possible to 'source' data validation/dependant lists from
seperate worksheet within the same workbook. I know it is possibl
when using only data validation lists, but when I try to sourc
dependant lists using '=INDIRECT(Sheet2!A2) for example, Excel say
this is not allowed??

Kind regards

And
 
Dave,

Thanks for your response. I have actually been using Debra's site to
learn about data validation. However, from my understanding she does
not explain how to source dependent lists from seperate worksheets.
She does explain that by naming ranges it is possible to source basic
data validation lists. However, her section on dependent lists says
that "In the Source box, type an equal sign and INDIRECT function,
referring to the first data cell in the Category column:
=INDIRECT(A2)". When I have tried substituting (A2) with the name of
my category column, the dependent list will only give options to one
particular category, irrespective of what category I have chosen.

Kind regards

Andy
 
There's no reason you can't use lists on different sheets - you just
need to name your range that contains the first list. For example, name
the cell with the category selection, say, "Source". Then on a different
sheet, set the validation to

Allow: List
Source: =INDIRECT(Source)

See

ftp://ftp.mcgimpsey.com/excel/andyp161demo.xls

for an example
 
Andy

Yes, you need to name your list (range), for example 'mylist', then select
list from the options in data validation and type =mylist to identify the
list.
 
In the sample workbook for this page:

http://www.contextures.com/xlDataVal02.html
(the link is at the bottom of the page)

the lists are on a separate worksheet.

The reference to "the first data cell in the Category column" is the
first cell in which you've selected a category, not the list of categories.
 
Back
Top