Data Validation Error!
 

Hi All,

I use the following formula under InsertNmaeDefine
to name a range as "MemberList"

=OFFSET(Membership!$A$1,1,0,COUNTA(Membership!A:A)-1)

Then When i use in another sheet as a Cell Validation
Cutom formula =INDIRECT("Membership!Memberlist") it says
an error in location

At the same time if I use VBA to define "Memberlist" in
my Membership sheet (dynamic Range)and use the Formula in
Data Validation it works Fine

what would be the possible reason for the failure in the
first Case?

is it possible to define a name using my OFFSET Formula
and use the name in Datavalidation so that I can Avoid
running that part of code?

TIA
Soniya