Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenating two text strings, formula is right, cell output is w | Excel Worksheet Functions | |||
Formula for concatenating text with results from calculation | Excel Discussion (Misc queries) | |||
help text for user defined formula | Excel Worksheet Functions | |||
Defined names | Excel Discussion (Misc queries) | |||
Concatenating cell values to create sheet names | Excel Discussion (Misc queries) |