Dependent List (via Data Validation) Error

[Dezdan]

Using the Tutorial here:
http://www.contextures.com/xlDataVal02.html
I created a dependent list via data validation. My data for both the
main list and the dependent list are on the same sheet, though the
lists are being used on another sheet in the same workbook. My trouble
is creating the dependent data validation using the INDIRECT function.
I am using: =INDIRECT(Data!A2) for which I receive the following
error: "You may not use references to other worksheets or workbooks for
Data Validation criteria." This is where I am currently stuck. I
*really* do not want to have the data for the list on the same sheet
that I am working on. Any help? Is there another way to create a
dependent list and have it work across worksheets in the same workbook?

Thanks,
~Dan

 

[Biff]

Hi!

Just give Data!A2 a defined name and then refer to that defined name:

Insert>Name>Define
Name: DataA2
Refers to: =Data!$A$2

Then:

=INDIRECT(DataA2)

Using that type of naming convention allows you to easily identify DataA2 to
mean Data!$A$2. (at least it's easy for me!)

Biff

 

[Dezdan]

Thanks Biff!!! Your post allowed me to see that I was totally screwing
up and making things way harder then they had to be. All fixed now!

Thanks,
~Dan