Linefeed Character

[Jen]

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

 

[Guest]

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

 

[Bernard Liengme]

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!

 

[Guest]

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

 

[Jen]

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

 

[Guest]

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