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.