On Jun 15, 9:30*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
... none of it's working now.

Can you describe what isn't "working now"... what do you see happen (or
not
happen)?

Here is what I think your post asked for (if this is incorrect, then you
will need to post a more detailed description, with examples perhaps, of
what you actually want). If an entry in the range of AC8 through to the
end
of Row 8 contains an a single character entry, then you want to
concatenate
the contents of the cell in the same column, but in Row 3, with other
with
the other cells from Row 3 whose 8th row counterparts also contain a
single
character entries in Row 3 (starting at Column "AC"). Unless I have
missed
something, that is what the code I posted (the Worksheet Change event
code,
not the function one) does.

Yes, thanks, it's going all right now. *But... the formula I typed in
X8 has disappeared, there's only the concatenated text. *And i want to
copy it down to 9 rows below. *Will I have to just type it in 9 more
times?

You **do** want to copy it down.... okay, I could adjust the range to do
that, but I'm thinking maybe a User Defined Function (UDF) is a better
choice because it will be more flexible for how you want this functionality
to be implemented. Give this UDF a try and see if it works for you
(remember, the UDF code goes into a Module... key in Alt+F11 from the
worksheet, then Insert/Module from the menu bar)...

Function BigConcatenate(RangeToTest As Range, ConcatRow As Long) As String
* Dim C As Range
* For Each C In RangeToTest
* * If Len(C.Value) = 1 Then
* * * BigConcatenate = BigConcatenate & Cells(ConcatRow, C.Column)..Value
* * End If
* Next
End Function

The BigConcatenate UDF requires 2 arguments... the range you are going to
test for having a single character and the row number (not a range, but a
number) containing the text you want to concatenate. This is the formula you
would put in X8...

=BigConcatenate(AC8:IV8,3)

This formula can be copied down.

Rick- Hide quoted text -

- Show quoted text -

It appears to be working, but I'll give it a full test later. Thanks
for persevering, you've been very helpful. Next hurdle: Boss who's a
bit backward-looking. (My last suggestion: 'Have a shared workbook
for staff to record their daily work on, then managers can look at it
at any time and extract any kind of stats from it that they want.'
'Staff are happy to continue writing their daily work down on the
paper sheets and handing them in, it's easier' What can you do?!?!?!?)