concatenate or &

workingboy

New Member
Joined
Dec 21, 2005
Messages
2
Simple question on concatenate or &, because I could've sworn I was able to do this before, but unable to do so now.

In a chart, I want to reference in the title box a cell, along with additional text. So, for example, I would put in the formula bar: ="This is a test for "&sheet1!$a$2

When I press return, I get either a long box that gives me numerous reasons why it's not working, or a short box saying that this function is not valid (when I use concatenate instead of &). And single quotes around sheet 1 does not help it work. I'm sure I was able to do this before, but perhaps I'm using a new computer or a different Excel version that is making a difference. However, I know I had 2003 Excel on previous computer where this function was working, and still have 2003 Excel on this computer, so I don't think it's the version.

Any help would be appreciated. Thanks.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Well, I can nicely replicate your situation. I am not finding a solution, not even sneaking in with a named formula. I would just create a helper-cell with the concatenation and reference the helper-cell and get on down the road.
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Aladin, I am using XL2003 also, and my results are exactly as workingboy describes. If I try to enter a formula in the formula bar with the chart title selected I get the errors he is describing:<ul>[*]="A"&A1 --> verbose error;[*] =concatenate("a",A1) --> succint error message.[/list]
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

Could this be a US vs European version issue?
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Hmmm, interesting thought. What are your results, Jon? If yours are as mine, then maybe we can drag Erik or Fairwinds into this too.. :)
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

Don't have 2003, but if the OP has US version then maybe, if he's on European then can't be...
 

workingboy

New Member
Joined
Dec 21, 2005
Messages
2
At least I know I'm not going crazy. As I've said before, I know I was able to do so before, but now unable to.

I have a US version of Excel 2003 with SP-2. Be glad to offer any other info if it helps.

I could take the easy way out and reference a single cell with all my stuff in it, but now this little problem bothers me.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
workingboy said:
At least I know I'm not going crazy. As I've said before, I know I was able to do so before, but now unable to.

I have a US version of Excel 2003 with SP-2. Be glad to offer any other info if it helps.

I could take the easy way out and reference a single cell with all my stuff in it, but now this little problem bothers me.

All my settings wrt OS and Office are US (English).
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Greg Truby said:
Aladin, I am using XL2003 also, and my results are exactly as workingboy describes. If I try to enter a formula in the formula bar with the chart title selected I get the errors he is describing:<ul>[*]="A"&A1 --> verbose error;[*] =concatenate("a",A1) --> succint error message.[/list]

Greg: you want me to send you a test workbook?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,055
Messages
5,569,936
Members
412,299
Latest member
agentless
Top