I am making a sheet where for example 'the cell A1 has a data validation
list' and depending on the selection in A1 cell the data validation list in
cell B1 will change.
for example:-cell A1 contains the list of team leaders depending on the
selection of the team leader from the list in A1 i should get a list of his
team members in cell B1.

Is that possible using data validation or do i need to use macros to get
that functionality. can anyone help me with that.

This is possible without using a macro. You will have to use INDIRECT and IF
of for this

here's how this will work
Assume that you have col A, B and C for 3 different teamleaders
in cell D1 you have TL name
in cell E1 you have team member drop down
now, for on the validation screen for cell E1 type =INDIRECT(Z1)
in cell Z1 type in IF formula such as =IF(D1="abc","A1:A20","B1:B20")
change the IF formula as per your need.

How does this work???

Depending on TL name selected a range constant will be stored in cell Z1
which represnts the list of team members
For the drop down validation INDIRECT will repturn this cell address and the
values in A1:A20 will be used.

hope this helps!!!

--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"gabbar singh" wrote:

I am making a sheet where for example 'the cell A1 has a data validation
list' and depending on the selection in A1 cell the data validation list in
cell B1 will change.
for example:-cell A1 contains the list of team leaders depending on the
selection of the team leader from the list in A1 i should get a list of his
team members in cell B1.

Is that possible using data validation or do i need to use macros to get
that functionality. can anyone help me with that.

thanks prannav. that helped. also found an option to use named lists for the
same issue.
but if i use named lists with the indirect function i cannot use spaces in
team leader names. i need to specify each team leaders members in each team
leadrs group as a named list by team leaders name.

is there a work around to this.

"Pranav Vaidya" wrote:

This is possible without using a macro. You will have to use INDIRECT and IF
of for this

here's how this will work
Assume that you have col A, B and C for 3 different teamleaders
in cell D1 you have TL name
in cell E1 you have team member drop down
now, for on the validation screen for cell E1 type =INDIRECT(Z1)
in cell Z1 type in IF formula such as =IF(D1="abc","A1:A20","B1:B20")
change the IF formula as per your need.

How does this work???

Depending on TL name selected a range constant will be stored in cell Z1
which represnts the list of team members
For the drop down validation INDIRECT will repturn this cell address and the
values in A1:A20 will be used.

hope this helps!!!

--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"gabbar singh" wrote:

I am making a sheet where for example 'the cell A1 has a data validation
list' and depending on the selection in A1 cell the data validation list in
cell B1 will change.
for example:-cell A1 contains the list of team leaders depending on the
selection of the team leader from the list in A1 i should get a list of his
team members in cell B1.

Is that possible using data validation or do i need to use macros to get
that functionality. can anyone help me with that.