ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Dependent List Query (https://www.excelbanter.com/excel-worksheet-functions/5228-dependent-list-query.html)

John

Dependent List Query
 
I am trying to set up a Dependent List and am having some difficulty. The
scenario is this

I have a list of Cities to which employees are attached to. I wish to select
a City and then only to be able to select an employee that is attached to
that list. The Cities have a Range Name "CityCol" and the Employees are
listed in columns according to the City they work in, so London employees
are in AA; Berlin AB; Paris AC ... etc

I select the City in C5 - that's not a problem I can do that. I then want to
select an Employee attached only to the City selected in C5 from a drop down
list - this is where I have hit a problem. The formula I have been
attempting to use is with Data Validation - List and is:

=OFFSET(INDIRECT($C5),0,0,COUNTA(INDIRECT(C5&"Col" )),1)

This returns an error "The source currently evaluates to an error"

Don't know if I am even using the appropriate formula

Thanks




mzehr

Hi John,
Have a lood at:
http://www.contextures.com/xlDataVal02.html


"John" wrote:

I am trying to set up a Dependent List and am having some difficulty. The
scenario is this

I have a list of Cities to which employees are attached to. I wish to select
a City and then only to be able to select an employee that is attached to
that list. The Cities have a Range Name "CityCol" and the Employees are
listed in columns according to the City they work in, so London employees
are in AA; Berlin AB; Paris AC ... etc

I select the City in C5 - that's not a problem I can do that. I then want to
select an Employee attached only to the City selected in C5 from a drop down
list - this is where I have hit a problem. The formula I have been
attempting to use is with Data Validation - List and is:

=OFFSET(INDIRECT($C5),0,0,COUNTA(INDIRECT(C5&"Col" )),1)

This returns an error "The source currently evaluates to an error"

Don't know if I am even using the appropriate formula

Thanks





Frank Kabel

Hi
another alternative:
http://www.xldynamic.com/source/xld.Dropdowns.html

--
Regards
Frank Kabel
Frankfurt, Germany

"John" schrieb im Newsbeitrag
...
I am trying to set up a Dependent List and am having some difficulty.

The
scenario is this

I have a list of Cities to which employees are attached to. I wish to

select
a City and then only to be able to select an employee that is

attached to
that list. The Cities have a Range Name "CityCol" and the Employees

are
listed in columns according to the City they work in, so London

employees
are in AA; Berlin AB; Paris AC ... etc

I select the City in C5 - that's not a problem I can do that. I then

want to
select an Employee attached only to the City selected in C5 from a

drop down
list - this is where I have hit a problem. The formula I have been
attempting to use is with Data Validation - List and is:

=OFFSET(INDIRECT($C5),0,0,COUNTA(INDIRECT(C5&"Col" )),1)

This returns an error "The source currently evaluates to an error"

Don't know if I am even using the appropriate formula

Thanks






All times are GMT +1. The time now is 11:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com