Conditional List (auto filtered)

[Guest]

Hi

I have 2 columns list (on column A and B). The first Column (A) has 20
working teams (from an organization RBS), and the other columnn list (B) has
200 resources - about 10 resources for each team.

I want to have 2 data-validation lists. When the user select a certain team
from the drop down list at column C (of 20 teams in the list), he will only
get the relevant resources in column D (an automated filtered list that
matches the relevant resources to the certian team selected in column C).

Thanks in advance for any help on this matter.

Regards,
Shai

 

[Ed Ferrero]

Hi Shai,

There is a sample of how to do this here -
http://www.edferrero.com/CascadingCellValidation.xls

Ed Ferrero (who will one day update his website)

 

[Guest]

I looked at your Excel file.
I don't understand how exactly to configure the 2 drop-down Data Validation,
especially the second one that shows only the values of the range
corresponding with the type of animal?

Thanks for advance for any help on this matter.

Regards,
Shai

 

[Ed Ferrero]

Hi Shai,

The second data validation cell is set to List and the list source is set as
a formula =INDIRECT($A$2)

Now, A2 is the address for the first data validation cell. If this contains
a value like 'Cats', then the INDIRECT formula points to the address of
'Cats'. Since 'Cats' is the named range $E$2:$E$4, the validation list is
set to that range.

When the value of cell A2 changes to 'Dogs', the INDIRECTR formula returns
the named range 'Dogs', which is $F$2:$F$4.

The trick is to set the data validation list for cell A2 to a list of named
ranges previously defined in the worksheet. Use menu item Insert -> Name ->
Define to see the named ranges in the worksheet. (Or Formulas -> Name
Manager if you are using Excel 2007).

Ed Ferrero

 

[Guest]

Thanks Ed, it works great now.

Regards,
Shai

Hi Shai,

The second data validation cell is set to List and the list source is set as
a formula =INDIRECT($A$2)

Now, A2 is the address for the first data validation cell. If this contains
a value like 'Cats', then the INDIRECT formula points to the address of
'Cats'. Since 'Cats' is the named range $E$2:$E$4, the validation list is
set to that range.

When the value of cell A2 changes to 'Dogs', the INDIRECTR formula returns
the named range 'Dogs', which is $F$2:$F$4.

The trick is to set the data validation list for cell A2 to a list of named
ranges previously defined in the worksheet. Use menu item Insert -> Name ->
Define to see the named ranges in the worksheet. (Or Formulas -> Name
Manager if you are using Excel 2007).

Ed Ferrero