Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Character | Excel Worksheet Functions | |||
Excel-Match 1st text character in a string to a known character? | Excel Worksheet Functions | |||
Can I create a special character for the Character Map? | Excel Discussion (Misc queries) | |||
Character Set | Excel Discussion (Misc queries) | |||
If Character #8 = C | Excel Worksheet Functions |