Data Validation

[Guest]

Hi
Is it possible to use a vlookup in cell data validation. In cell A1 i have
the team name and in a2 the following formula =INDIRECT(SUBSTITUTE(A$296,"
","")) to look up team members. From time to Time some team members cant
make it and susbstitues are arranged. I have Cell B2 Set aside to put an 'S'
for any substitutes that may bowl. What i need is a forumula to look at B2
if it equals S lookup substitutes list if not look up A1. (team Name)

Is it possible to do this with data vailidation

thanks

 

[Debra Dalgleish]

Assuming the list of subsititutes is names Subs:

=IF(B2="S",Subs,INDIRECT(SUBSTITUTE(A1," ","")))

 

[Guest]

Thanks Alot Debra works great.

Assuming the list of subsititutes is names Subs:

=IF(B2="S",Subs,INDIRECT(SUBSTITUTE(A1," ","")))