Assuming the list of subsititutes is names Subs:

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

Andrew C wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html