Linefeed Character
 

Hello there,

I would like to have my Text box /Chart-title consisting out of 2 cells that
exist on my sheet.
I would have guessed that I can concatenate both cells into that object but
it doesnt.

Eg.
A1= Bob
A2= Andy

Object = A1 (the cell-reference) will show "Bob" in the object
Object = A1&A2 , Excel will not allow you this ...
Of course I can concatenate both cell on my worksheet and and link this 3rd
cell...

But actually,what I would like to achieve, is to link both cells to that
object ... where they both appear UNDERNEATH eachother: So that "Andy"
appears under "Bob", concatenating some "linefeed" character -if that
exists- ....

Any ideas?

Jen

Linefeed Character
 

one way to do it
move Andy to A3
in A2 enter a space and Alt-enter

in your title
=sheet1!A1:A3

with what you had =A1:A2 would have put bob andy as the title

this does not seem to work with text boxes

"Jen" wrote:

Hello there,

I would like to have my Text box /Chart-title consisting out of 2 cells that
exist on my sheet.
I would have guessed that I can concatenate both cells into that object but
it doesnt.

Eg.
A1= Bob
A2= Andy

Object = A1 (the cell-reference) will show "Bob" in the object
Object = A1&A2 , Excel will not allow you this ...
Of course I can concatenate both cell on my worksheet and and link this 3rd
cell...

But actually,what I would like to achieve, is to link both cells to that
object ... where they both appear UNDERNEATH eachother: So that "Andy"
appears under "Bob", concatenating some "linefeed" character -if that
exists- ....

Any ideas?

Jen

Linefeed Character
 

I have never tried this before (I do recall years ago a spreadsheet that had
Title1 and Title2 features)
I was unable to get any text box to work with a concatenation formula such
as =DataSheet!$A$1&DataSheet!$A$2
I put this formula into D1 =A1&CHAR(10)&A2 and used that cell for my chart
title; this gives the effect you want.
I could not find a way to avoid a third cell - I tried to used a defined
name but no luck.
Perhaps Jon Peltier can help us here!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jen" wrote in message
...
Hello there,

I would like to have my Text box /Chart-title consisting out of 2 cells
that exist on my sheet.
I would have guessed that I can concatenate both cells into that object
but it doesnt.

Eg.
A1= Bob
A2= Andy

Object = A1 (the cell-reference) will show "Bob" in the object
Object = A1&A2 , Excel will not allow you this ...
Of course I can concatenate both cell on my worksheet and and link this
3rd cell...

But actually,what I would like to achieve, is to link both cells to that
object ... where they both appear UNDERNEATH eachother: So that "Andy"
appears under "Bob", concatenating some "linefeed" character -if that
exists- ....

Any ideas?

Jen

Linefeed Character
 

you can use just the tqwo cells a1:A2 if you put the alt-enter in one of them.
It might screw up the format of your sheet

"bj" wrote:

one way to do it
move Andy to A3
in A2 enter a space and Alt-enter

in your title
=sheet1!A1:A3

with what you had =A1:A2 would have put bob andy as the title

this does not seem to work with text boxes

"Jen" wrote:

Hello there,

I would like to have my Text box /Chart-title consisting out of 2 cells that
exist on my sheet.
I would have guessed that I can concatenate both cells into that object but
it doesnt.

Eg.
A1= Bob
A2= Andy

Object = A1 (the cell-reference) will show "Bob" in the object
Object = A1&A2 , Excel will not allow you this ...
Of course I can concatenate both cell on my worksheet and and link this 3rd
cell...

But actually,what I would like to achieve, is to link both cells to that
object ... where they both appear UNDERNEATH eachother: So that "Andy"
appears under "Bob", concatenating some "linefeed" character -if that
exists- ....

Any ideas?

Jen

Linefeed Character
 

Hi Bernard,

Thanks for the help! Your method requires a helper cell but it allows input
in a chart title as well as "texbox" ...!
Bj's solution works only with a "charttitle", couldn't manage it work on a
textbox , autoshape or any other ...
Thanks for your insight too though BJ! I did not get that far myself ;)

We'll see whether Jon or anybody else comes up with a solution without a
helper cell. But I am already very happy with your solution Bernard!
Thanks for that,
Jen

"Bernard Liengme" wrote in message
...
I have never tried this before (I do recall years ago a spreadsheet that
had Title1 and Title2 features)
I was unable to get any text box to work with a concatenation formula such
as =DataSheet!$A$1&DataSheet!$A$2
I put this formula into D1 =A1&CHAR(10)&A2 and used that cell for my chart
title; this gives the effect you want.
I could not find a way to avoid a third cell - I tried to used a defined
name but no luck.
Perhaps Jon Peltier can help us here!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jen" wrote in message
...
Hello there,

I would like to have my Text box /Chart-title consisting out of 2 cells
that exist on my sheet.
I would have guessed that I can concatenate both cells into that object
but it doesnt.

Eg.
A1= Bob
A2= Andy

Object = A1 (the cell-reference) will show "Bob" in the object
Object = A1&A2 , Excel will not allow you this ...
Of course I can concatenate both cell on my worksheet and and link this
3rd cell...

But actually,what I would like to achieve, is to link both cells to that
object ... where they both appear UNDERNEATH eachother: So that "Andy"
appears under "Bob", concatenating some "linefeed" character -if that
exists- ....

Any ideas?

Jen

Linefeed Character
 

of course you could use a macro , but the helper cell is easier

"Jen" wrote:

Hi Bernard,

Thanks for the help! Your method requires a helper cell but it allows input
in a chart title as well as "texbox" ...!
Bj's solution works only with a "charttitle", couldn't manage it work on a
textbox , autoshape or any other ...
Thanks for your insight too though BJ! I did not get that far myself ;)

We'll see whether Jon or anybody else comes up with a solution without a
helper cell. But I am already very happy with your solution Bernard!
Thanks for that,
Jen

"Bernard Liengme" wrote in message
...
I have never tried this before (I do recall years ago a spreadsheet that
had Title1 and Title2 features)
I was unable to get any text box to work with a concatenation formula such
as =DataSheet!$A$1&DataSheet!$A$2
I put this formula into D1 =A1&CHAR(10)&A2 and used that cell for my chart
title; this gives the effect you want.
I could not find a way to avoid a third cell - I tried to used a defined
name but no luck.
Perhaps Jon Peltier can help us here!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jen" wrote in message
...
Hello there,

I would like to have my Text box /Chart-title consisting out of 2 cells
that exist on my sheet.
I would have guessed that I can concatenate both cells into that object
but it doesnt.

Eg.
A1= Bob
A2= Andy

Object = A1 (the cell-reference) will show "Bob" in the object
Object = A1&A2 , Excel will not allow you this ...
Of course I can concatenate both cell on my worksheet and and link this
3rd cell...

But actually,what I would like to achieve, is to link both cells to that
object ... where they both appear UNDERNEATH eachother: So that "Andy"
appears under "Bob", concatenating some "linefeed" character -if that
exists- ....

Any ideas?

Jen