how to add up string or text together

[Dumidu Roshan \(Hotmail\)]

hi everyone
I have a workbook with 12 sheets in them
it got 11 sheets for 11 employees say Fred Kathy mark so on...
every worksheet has row 30 to 33 to column A to U reserved for employee
comments
A30 to u30 is merged cell so is a31 to u31 and so on.. to type in long lines
of comments
main page adds up all the times they worked
in the main page I want to display comments from all the employee sheets
can you add text or string together like numbers? how do I do this?
thanks
dumi

 

[PaulW]

If in A1 I have "Hello"
in B1 I have "Dave"

If I use in C1 =CONCATENATE(A1,B1) the formula result is "HelloDave"

Note how A1 doesn't have a space after it, and B1 doesn't have a space
before, so theres no space. You can always do, =CONCATENATE(A1," ",B1) to
give "Hello Dave"

 

[Robert McCurdy]

So you have 3 long rows or 3 large merged cells, for workers to enter comments.
A30:A33 would be the range to look at per employee on each sheet.
Untested, but it should look something like this:

Sub GetComs()
dim i as long, ii as long, ComStr as string
For i = 2 to 12
for ii = 30 to 33
ComStr = worksheets(i).cells(ii,1).value & ": " & ComStr
next ii
'change this to you desired column and row start on sheet 1 if not J3
Sheets(1).cells( i + 2 , 10).value = _
"Comments from " & worksheets(i).name & " > " & ComStr
ComStr = "" 'clearing the old comments for the next
next i
End Sub

I've assumed that the sheet name represents an employee's name or ID.


Regards
Robert McCurdy

 

[Per Jessen]

Hi

You can use the formula below to concatentate the comments

=CONCATENATE(Sheet2!A1;Sheet3!A1)

BUT if the concatentated text is longer than the merged cells, then it won't
show the rest of the text.

I would rather use a formula like the one below to show all comments,
leaving cells empty if no entry was made. Then you can have a little macro
to hide empty rows in the comment area.

Best regards,
Per