Hello

In X8 I've got IF(LEN(AC8)=1,AC3,""), ie if formula in AC8 gives a
single letter (it will always be a single letter or blank), give AC3,
otherwise nothing. But I want X8 to contain the result for that
concatenated with the same result for AD3, AE3... up to BZ8 and
possibly beyond.

First enter this small UDF:

Function supercat(r As Range) As String
supercat = ""
For i = 29 To 78
If Len(Cells(8, i).Value) = 1 Then
supercat = supercat & Cells(3, i).Value
End If
Next
End Function

and then in cell X8 enter:

=supercat(AC8:BZ8)

Note that the argument of the UDF is not actually used by the UDF.
--
Gary''s Student - gsnu2007j


"robzrob" wrote:

Hello

In X8 I've got IF(LEN(AC8)=1,AC3,""), ie if formula in AC8 gives a
single letter (it will always be a single letter or blank), give AC3,
otherwise nothing. But I want X8 to contain the result for that
concatenated with the same result for AD3, AE3... up to BZ8 and
possibly beyond.

You cannot do what you want using worksheet functions, but you can do it
with a User Defined Function. Press Alt+F11 to get into the Visual Basic
editior and then click Insert/Module from its menu bar. When you do this, a
code window will open; copy/paste this code into that window...

Function BigConcatenate(R As Range) As String
Dim C As Range
For Each C In R
BigConcatenate = BigConcatenate & C.Value
Next
End Function

Now, when you go back to your worksheet, you will be able to use
BigConcatenate like a normal worksheet function within your formulas. To use
it, put in a range of cells and whatever is in those cells will be
concatenated together. For the condition you laid out, put this in X3...

=BigConcatenate(AC:IV)

That will automatically concatenate the cells from AC3 to the end (XL2003
and earlier) of your worksheet. This formula can be copied down if needed.

Rick


"robzrob" wrote in message
...
Hello

In X8 I've got IF(LEN(AC8)=1,AC3,""), ie if formula in AC8 gives a
single letter (it will always be a single letter or blank), give AC3,
otherwise nothing. But I want X8 to contain the result for that
concatenated with the same result for AD3, AE3... up to BZ8 and
possibly beyond.

Sorry, I gave the wrong worksheet formula for you to use. Instead of this...

=BigConcatenate(AC:IV)

use this...

X8: =IF(LEN(AC8)=1,BigConcatenate(AC3:IV3),"")

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
You cannot do what you want using worksheet functions, but you can do it
with a User Defined Function. Press Alt+F11 to get into the Visual Basic
editior and then click Insert/Module from its menu bar. When you do this,
a code window will open; copy/paste this code into that window...

Function BigConcatenate(R As Range) As String
Dim C As Range
For Each C In R
BigConcatenate = BigConcatenate & C.Value
Next
End Function

Now, when you go back to your worksheet, you will be able to use
BigConcatenate like a normal worksheet function within your formulas. To
use it, put in a range of cells and whatever is in those cells will be
concatenated together. For the condition you laid out, put this in X3...

=BigConcatenate(AC:IV)

That will automatically concatenate the cells from AC3 to the end (XL2003
and earlier) of your worksheet. This formula can be copied down if needed.

Rick


"robzrob" wrote in message
...
Hello

In X8 I've got IF(LEN(AC8)=1,AC3,""), ie if formula in AC8 gives a
single letter (it will always be a single letter or blank), give AC3,
otherwise nothing. But I want X8 to contain the result for that
concatenated with the same result for AD3, AE3... up to BZ8 and
possibly beyond.

Okay, I just read Gary''s Student's response and think I misunderstood your
request initially... I think I see what you want to do now. Since your cell
locations seem well fixed in position, instead of a UDF, I think worksheet
event code may be a better choice for you. Try this. Right-click the
worksheet tab where you want this functionality. That will take you to the
Visual Basic editor and put you in the code window for the worksheet whose
tab you right-clicked. Just copy paste the following code into that code
window...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim LastColumn As Long
Dim Concatenation As String
LastColumn = Cells(3, Columns.Count).End(xlToLeft).Column
If Intersect(Target, Union(Range("AC3").Resize(1, Columns.Count - 28), _
Range("AC8").Resize(1, Columns.Count - 28))) Is Nothing Then Exit Sub
For X = 29 To LastColumn
If Len(Cells(8, X).Value) = 1 Then
Concatenation = Concatenation & Cells(3, X).Value
End If
Next
Range("X8").Value = Concatenation
End Sub

Now, go back to the worksheet and type your single character entries into a
few of the cells in the range AC8:IV8. Cell X8 will show the concatenation
of the entries in AC3:IV3 corresponding to the single character entries you
made in AC8:IV8. This event code will react to changes made in either Rows 3
or 8 starting in Columns 29.

Rick


"robzrob" wrote in message
...
Hello

In X8 I've got IF(LEN(AC8)=1,AC3,""), ie if formula in AC8 gives a
single letter (it will always be a single letter or blank), give AC3,
otherwise nothing. But I want X8 to contain the result for that
concatenated with the same result for AD3, AE3... up to BZ8 and
possibly beyond.

On Jun 15, 5:27*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
Okay, I just read Gary''s Student's response and think I misunderstood your
request initially... I think I see what you want to do now. Since your cell
locations seem well fixed in position, instead of a UDF, I think worksheet
event code may be a better choice for you. Try this. Right-click the
worksheet tab where you want this functionality. That will take you to the
Visual Basic editor and put you in the code window for the worksheet whose
tab you right-clicked. Just copy paste the following code into that code
window...

Private Sub Worksheet_Change(ByVal Target As Range)
* Dim X As Long
* Dim LastColumn As Long
* Dim Concatenation As String
* LastColumn = Cells(3, Columns.Count).End(xlToLeft).Column
* If Intersect(Target, Union(Range("AC3").Resize(1, Columns.Count - 28), _
* * *Range("AC8").Resize(1, Columns.Count - 28))) Is Nothing Then Exit Sub
* For X = 29 To LastColumn
* * If Len(Cells(8, X).Value) = 1 Then
* * * Concatenation = Concatenation & Cells(3, X).Value
* * End If
* Next
* Range("X8").Value = Concatenation
End Sub

Now, go back to the worksheet and type your single character entries into a
few of the cells in the range AC8:IV8. Cell X8 will show the concatenation
of the entries in AC3:IV3 corresponding to the single character entries you
made in AC8:IV8. This event code will react to changes made in either Rows 3
or 8 starting in Columns 29.

Rick

"robzrob" wrote in message

...



Hello

In X8 I've got IF(LEN(AC8)=1,AC3,""), ie if formula in AC8 gives a
single letter (it will always be a single letter or blank), give AC3,
otherwise nothing. *But I want X8 to contain the result for that
concatenated with the same result for AD3, AE3... up to BZ8 and
possibly beyond.- Hide quoted text -

- Show quoted text -

Thanks. I'll try that. I was about to say that I'm copying down the
formula so that it applies to 10 rows - and neither your nor GS's
methods work.

On Jun 15, 5:27*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
Okay, I just read Gary''s Student's response and think I misunderstood your
request initially... I think I see what you want to do now. Since your cell
locations seem well fixed in position, instead of a UDF, I think worksheet
event code may be a better choice for you. Try this. Right-click the
worksheet tab where you want this functionality. That will take you to the
Visual Basic editor and put you in the code window for the worksheet whose
tab you right-clicked. Just copy paste the following code into that code
window...

Private Sub Worksheet_Change(ByVal Target As Range)
* Dim X As Long
* Dim LastColumn As Long
* Dim Concatenation As String
* LastColumn = Cells(3, Columns.Count).End(xlToLeft).Column
* If Intersect(Target, Union(Range("AC3").Resize(1, Columns.Count - 28), _
* * *Range("AC8").Resize(1, Columns.Count - 28))) Is Nothing Then Exit Sub
* For X = 29 To LastColumn
* * If Len(Cells(8, X).Value) = 1 Then
* * * Concatenation = Concatenation & Cells(3, X).Value
* * End If
* Next
* Range("X8").Value = Concatenation
End Sub

Now, go back to the worksheet and type your single character entries into a
few of the cells in the range AC8:IV8. Cell X8 will show the concatenation
of the entries in AC3:IV3 corresponding to the single character entries you
made in AC8:IV8. This event code will react to changes made in either Rows 3
or 8 starting in Columns 29.

Rick

"robzrob" wrote in message

...



Hello

In X8 I've got IF(LEN(AC8)=1,AC3,""), ie if formula in AC8 gives a
single letter (it will always be a single letter or blank), give AC3,
otherwise nothing. *But I want X8 to contain the result for that
concatenated with the same result for AD3, AE3... up to BZ8 and
possibly beyond.- Hide quoted text -

- Show quoted text -

... none of it's working now.

In X8 I've got IF(LEN(AC8)=1,AC3,""), ie if formula in AC8 gives a
single letter (it will always be a single letter or blank), give AC3,
otherwise nothing. But I want X8 to contain the result for that
concatenated with the same result for AD3, AE3... up to BZ8 and
possibly beyond.- Hide quoted text -

Okay, I just read Gary''s Student's response and think I misunderstood
your
request initially... I think I see what you want to do now. Since your
cell
locations seem well fixed in position, instead of a UDF, I think
worksheet
event code may be a better choice for you. Try this. Right-click the
worksheet tab where you want this functionality. That will take you to
the
Visual Basic editor and put you in the code window for the worksheet
whose
tab you right-clicked. Just copy paste the following code into that code
window...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim LastColumn As Long
Dim Concatenation As String
LastColumn = Cells(3, Columns.Count).End(xlToLeft).Column
If Intersect(Target, Union(Range("AC3").Resize(1, Columns.Count - 28), _
Range("AC8").Resize(1, Columns.Count - 28))) Is Nothing Then Exit Sub
For X = 29 To LastColumn
If Len(Cells(8, X).Value) = 1 Then
Concatenation = Concatenation & Cells(3, X).Value
End If
Next
Range("X8").Value = Concatenation
End Sub

Now, go back to the worksheet and type your single character entries
into a
few of the cells in the range AC8:IV8. Cell X8 will show the
concatenation
of the entries in AC3:IV3 corresponding to the single character entries
you
made in AC8:IV8. This event code will react to changes made in either
Rows 3
or 8 starting in Columns 29.

... 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.

Rick

On Jun 15, 7:57*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
In X8 I've got IF(LEN(AC8)=1,AC3,""), ie if formula in AC8 gives a
single letter (it will always be a single letter or blank), give AC3,
otherwise nothing. But I want X8 to contain the result for that
concatenated with the same result for AD3, AE3... up to BZ8 and
possibly beyond.- Hide quoted text -

Okay, I just read Gary''s Student's response and think I misunderstood
your
request initially... I think I see what you want to do now. Since your
cell
locations seem well fixed in position, instead of a UDF, I think
worksheet
event code may be a better choice for you. Try this. Right-click the
worksheet tab where you want this functionality. That will take you to
the
Visual Basic editor and put you in the code window for the worksheet
whose
tab you right-clicked. Just copy paste the following code into that code
window...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim LastColumn As Long
Dim Concatenation As String
LastColumn = Cells(3, Columns.Count).End(xlToLeft).Column
If Intersect(Target, Union(Range("AC3").Resize(1, Columns.Count - 28), _
Range("AC8").Resize(1, Columns.Count - 28))) Is Nothing Then Exit Sub
For X = 29 To LastColumn
If Len(Cells(8, X).Value) = 1 Then
Concatenation = Concatenation & Cells(3, X).Value
End If
Next
Range("X8").Value = Concatenation
End Sub

Now, go back to the worksheet and type your single character entries
into a
few of the cells in the range AC8:IV8. Cell X8 will show the
concatenation
of the entries in AC3:IV3 corresponding to the single character entries
you
made in AC8:IV8. This event code will react to changes made in either
Rows 3
or 8 starting in Columns 29.

... 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.

Rick- Hide quoted text -

- Show quoted text -

Hello Rick

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?

... 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

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?!?!?!?)