Soniya.

make sure the name is defined as a workbook name, and NOT as a worksheet
name. (you should be able to "see" it from any sheet.)

so it's name is
NOT sheetX!MemberList
BUT Memberlist


THEN

in data validation no need to use indirect

just do

type: list
source: =Memberlist
(press F3 in the box and the list of available names pops up)



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Soniya" wrote:

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

Hi,

Is it possible to define a Name as workbook Name by using
InsertDefineNames or i have to use code?

Soniya


-----Original Message-----
Soniya.

make sure the name is defined as a workbook name, and
NOT as a worksheet
name. (you should be able to "see" it from any sheet.)

so it's name is
NOT sheetX!MemberList
BUT Memberlist


THEN

in data validation no need to use indirect

just do

type: list
source: =Memberlist
(press F3 in the box and the list of available names
pops up)



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Soniya" wrote:

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


.

manual or vba = same :)

just make sure you define it as Memberlist
not as sheet1!memberlist


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Soniya" wrote:

Hi,

Is it possible to define a Name as workbook Name by using
InsertDefineNames or i have to use code?

Soniya


-----Original Message-----
Soniya.

make sure the name is defined as a workbook name, and
NOT as a worksheet
name. (you should be able to "see" it from any sheet.)

so it's name is
NOT sheetX!MemberList
BUT Memberlist


THEN

in data validation no need to use indirect

just do

type: list
source: =Memberlist
(press F3 in the box and the list of available names
pops up)



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Soniya" wrote:

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


.