Help obtaining value of defined names when concatenating text with formula
 

How can we refer to the value of a defined name in a formula in Excel 2003
by combining text with a formula?



My formula is: =rate&RIGHT(TEXT(YEAR(A12);"#");2)

(";" instead of "," because of my regional setting.)



This formula produces a #NAME error.



When I enter the formula by simply typing the name =rate09 I obtain the
correct value defined for rate09, so my name is defined correctly.



When I put quotes around "rate" in above formula:
="rate"&RIGHT(TEXT(YEAR($A12);"##");2))

I obtain the correct name (rate09), so I assume that my formula to extract a
2 digit year number from my date field is correct also.



But when I'm combining text with a formula which extracts the year portion
of a date field and convert it to text, it does not recognize my defined
name.



What am I missing?



Please help...



Thanks,



M. Beauchamp

Help obtaining value of defined names when concatenating text with
 

enclose your formula within Indirect ..something like this

=indirect("rate"&RIGHT(TEXT(YEAR(A12);"#");2))


"mbeauchamp" wrote:

How can we refer to the value of a defined name in a formula in Excel 2003
by combining text with a formula?



My formula is: =rate&RIGHT(TEXT(YEAR(A12);"#");2)

(";" instead of "," because of my regional setting.)



This formula produces a #NAME error.



When I enter the formula by simply typing the name =rate09 I obtain the
correct value defined for rate09, so my name is defined correctly.



When I put quotes around "rate" in above formula:
="rate"&RIGHT(TEXT(YEAR($A12);"##");2))

I obtain the correct name (rate09), so I assume that my formula to extract a
2 digit year number from my date field is correct also.



But when I'm combining text with a formula which extracts the year portion
of a date field and convert it to text, it does not recognize my defined
name.



What am I missing?



Please help...



Thanks,



M. Beauchamp

Help obtaining value of defined names when concatenating text with formula
 

Have you tried =INDIRECT("rate"&RIGHT(TEXT(YEAR(A12);"#");2)) ?
--
David Biddulph

"mbeauchamp" wrote in message
...
How can we refer to the value of a defined name in a formula in Excel 2003
by combining text with a formula?



My formula is: =rate&RIGHT(TEXT(YEAR(A12);"#");2)

(";" instead of "," because of my regional setting.)



This formula produces a #NAME error.



When I enter the formula by simply typing the name =rate09 I obtain the
correct value defined for rate09, so my name is defined correctly.



When I put quotes around "rate" in above formula:
="rate"&RIGHT(TEXT(YEAR($A12);"##");2))

I obtain the correct name (rate09), so I assume that my formula to extract
a 2 digit year number from my date field is correct also.



But when I'm combining text with a formula which extracts the year portion
of a date field and convert it to text, it does not recognize my defined
name.



What am I missing?



Please help...



Thanks,



M. Beauchamp

Help obtaining value of defined names when concatenating text with
 

Wow... that was fast and efficient...

Thank you very much N harkawat

mbeauchamp

"N harkawat" wrote in message
...
enclose your formula within Indirect ..something like this

=indirect("rate"&RIGHT(TEXT(YEAR(A12);"#");2))


"mbeauchamp" wrote:

How can we refer to the value of a defined name in a formula in Excel
2003
by combining text with a formula?



My formula is: =rate&RIGHT(TEXT(YEAR(A12);"#");2)

(";" instead of "," because of my regional setting.)



This formula produces a #NAME error.



When I enter the formula by simply typing the name =rate09 I obtain the
correct value defined for rate09, so my name is defined correctly.



When I put quotes around "rate" in above formula:
="rate"&RIGHT(TEXT(YEAR($A12);"##");2))

I obtain the correct name (rate09), so I assume that my formula to
extract a
2 digit year number from my date field is correct also.



But when I'm combining text with a formula which extracts the year
portion
of a date field and convert it to text, it does not recognize my defined
name.



What am I missing?



Please help...



Thanks,



M. Beauchamp