Help obtaining value of defined names when concatenating text with formula

M

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

N harkawat

enclose your formula within Indirect ..something like this

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

mbeauchamp

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

Thank you very much N harkawat

mbeauchamp
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top