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

--
Message posted from http://www.ExcelForum.com

If you give that range a name, you can use it in the Data|validation dialog.

Debra Dalgleish explains it at:
http://www.contextures.com/xlDataVal01.html#Name

"andyp161 <" wrote:
>
> Hi there,
>
> Is it possible to 'source' data validation/dependant lists from a
> seperate worksheet within the same workbook. I know it is possible
> when using only data validation lists, but when I try to source
> dependant lists using '=INDIRECT(Sheet2!A2) for example, Excel says
> this is not allowed??
>
> Kind regards
>
> Andy
>
> ---
> Message posted from http://www.ExcelForum.com/

--

Dave Peterson
(E-Mail Removed)

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


---
Message posted from http://www.ExcelForum.com/

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





In article <(E-Mail Removed)>,
andyp161 <<(E-Mail Removed)>> wrote:

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

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.

"andyp161 >" wrote:

> Hi there,
>
> Is it possible to 'source' data validation/dependant lists from a
> seperate worksheet within the same workbook. I know it is possible
> when using only data validation lists, but when I try to source
> dependant lists using '=INDIRECT(Sheet2!A2) for example, Excel says
> this is not allowed??
>
> Kind regards
>
> Andy
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>
>

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.

andyp161 < wrote:
> 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.

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html