In sheet1 a1, I have datavalidation list(named range) consisting of sheet
names of my w/book. Some of the names in that list has spaces (example: John
Smith).In some other cell say in d1, I am using formula
d1=indirect(a1&"!c2").The formula is working with sheet names without spaces
in my list .Its not working with sheet name with spaces like "John Smith"
sheet. how to get it ?.I can't change/modify names in my list as they are
derived from a named range of other w/book.

Use

=indirect("'"&a1&"'!c2").

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"TUNGANA KURMA RAJU" wrote in
message ...
In sheet1 a1, I have datavalidation list(named range) consisting of sheet
names of my w/book. Some of the names in that list has spaces (example:
John
Smith).In some other cell say in d1, I am using formula
d1=indirect(a1&"!c2").The formula is working with sheet names without
spaces
in my list .Its not working with sheet name with spaces like "John Smith"
sheet. how to get it ?.I can't change/modify names in my list as they are
derived from a named range of other w/book.

Thanks Bob! You are always helpful to me

"Bob Phillips" wrote:

Use

=indirect("'"&a1&"'!c2").

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"TUNGANA KURMA RAJU" wrote in
message ...
In sheet1 a1, I have datavalidation list(named range) consisting of sheet
names of my w/book. Some of the names in that list has spaces (example:
John
Smith).In some other cell say in d1, I am using formula
d1=indirect(a1&"!c2").The formula is working with sheet names without
spaces
in my list .Its not working with sheet name with spaces like "John Smith"
sheet. how to get it ?.I can't change/modify names in my list as they are
derived from a named range of other w/book.

Thank-you, it is my pleasure.

Bob

"TUNGANA KURMA RAJU" wrote in
message ...
Thanks Bob! You are always helpful to me

"Bob Phillips" wrote:

Use

=indirect("'"&a1&"'!c2").

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"TUNGANA KURMA RAJU" wrote
in
message ...
In sheet1 a1, I have datavalidation list(named range) consisting of
sheet
names of my w/book. Some of the names in that list has spaces
(example:
John
Smith).In some other cell say in d1, I am using formula
d1=indirect(a1&"!c2").The formula is working with sheet names without
spaces
in my list .Its not working with sheet name with spaces like "John
Smith"
sheet. how to get it ?.I can't change/modify names in my list as they
are
derived from a named range of other w/book.