[Solved] Poss going about this all wrong..

[esporter]

[Solved]

Hi Gurus!

So imagine I have a spreadsheet, with one column of 10,000 names, like "Joe", "Mary", "Sam", etc.

I want each of these entries to actually say "Happy Birthday, Joe" and the next to say "Happy Birthday, Mary",etc.

Now I can type "Happy Birthday," in every #&$^ cell at the start of each line, but I shouldn't have to...

I am working off a spreadsheet that was given to me and can't have the columns altered, etc, so don't suggest I change then re-import the text. I have to work with this sheet. And the entry is more complicated than just a name, I'm simplifying...

I tried creating a macro; I recorded one, to go to the beginning of the cell, enter the text, then move down one cell, and repeat. I have worked with Excel but not OO. I found there was no way to assign this macro an F-key and just run it?? So I gave up on macros for now.

I created a column at the side of the names and had them all say Happy Birthday in them, and tried to merge the columns and keep the text. This seems only doable one row at a time. Again, I don't think I should need to sit here and do 10,000 "merge cells" operations.

I begin to think I'm missing some obvious way to do this. I'm starting to feel very dumb. The tutorials I dug up were dealing with creating charts and merging those cells, or with spreadsheets, but not in this particular way.

I looked at CONCATENATE but wouldn't I have to program every cell? Wouldn't it be easier to just merge them in some way, or autotype them in?

[ozzie]

Your names are in column A1 and down
In a cell off to the side somewhere type Happy Birthday, (this should include a space - F1
in my formula)
Put this formula in B1

[esporter]

thank you - it does not matter where I write "Happy Birthday "?

I actually created a spreadsheet like this for practice

Your function does pick up the name in the A column, but only repeats that name in the B column. It does that well. I haven't gotten it to pick up any additional text and add it though. I appreciate your patience.

[scsisys]

esporter...

It makes no difference what cell (other than a cell in the Name column) you place
Happy Birthday.

The formula below assumes Happy Birthday is in D1 and the Names starting in A1.
You have to assign an absolute address $ to the cell row number containing the
Happy Birthday text. The &" "& CONCATENATEs the two text fields and adds a
space between Birthday and the name in A1 ( and subsequent cells ).

=D$1&" "&A1

scsisys
_________________
OO 3.2.1
Win XP /SP3

[ozzie]

In my example 'Happy birthday' is in F1
If you put 'Happy birthday' in G17 then change the formula from $F$1 to $G$17
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).

[Sliderule]

There is another technique or trick to use to get the results you have described. That is to create / use a Style that is contains a Format that includes the text you want.

Steps to follow:

1. Open your Calc file
   
   
2. Press the F11 key ( or, from the Menu: Format -> Styles and Formatting F11 )
   
   
3. Right click on the Default style, and, select New... from the pop-up
   
   
4. Under tab, Organizer, assign a new Name for the Style, perhaps entering, Happy Birthday
   
   
5. Click on Numbers tab
   
   
6. Under Category , click on Text ( at the bottom )
   
   
7. In the Format code box, enter:
   

   Code:

   Happy Birthday, @

   
   
8. Press the OK button
   
   
9. Select the range of cells with your name ( for example, A2:A10000 ), and, in the Styles and Formatting window, double click on your new Style
   
   
10. Smile and say: "Gee Sliderule, that was easy, all I have to do is use Styles to format the text strings the way I want them to look!"
    
    
11. Think to yourself, "now, all I have to do is follow the instructions at the bottom of Sliderule's post and mark the forum post as [Solved] "

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.

[esporter]

Both are interesting approaches and I'll play with them for other reasons in the sheet, also. Thank you!