Help obtaining value of defined names when concatenating text with formula

[mbeauchamp]

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

 

[N harkawat]

enclose your formula within Indirect ..something like this

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

 

[David Biddulph]

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

 

[mbeauchamp]

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

Thank you very much N harkawat

mbeauchamp