OK. Back again. For Invoice summary, I pasted this UDF code (shamelessly copied from Tigeravatar) into a module (right click on sheet name)
Public Function ConcatAll(ByVal varData As Variant, Optional ByVal sDelimiter As String = vbNullString, Optional ByVal bUnique As Boolean = False) As String
Dim DataIndex As Variant
Dim strResult As String
If IsArray(varData) _
Or TypeOf varData Is Range _
Or TypeOf varData Is Collection Then
For Each DataIndex In varData
If Len(DataIndex) > 0 Then
If bUnique = True Then
If InStr(1, "||" & strResult & "||", "||" & DataIndex & "||", vbTextCompare) = 0 Then
trResult = strResult & "||" & DataIndex
End If
Else
strResult = strResult & "||" & DataIndex
End If
End If
Next DataIndex
strResult = Replace(Mid(strResult, 3), "||", sDelimiter)
Else
strResult = varData
End If
ConcatAll = strResult
End Function
It takes the form:
=concatall(IF('Inv Details'!$A$5:$A$198='Invoice Summary'!A6,'Inv Details'!$F$5:$F$198,""),", ")
Syntax: = ConcatAll(if(range=criterion, TRUE - do this, FALSE - do that,separator).
On Invoice summary, I killed a lot of columns and put 1 line = 1 item. Much easier to copy formulae down. You can always set the print/pdf area to landscape to accommodate this change. Your Unit Prices aren't there, so I couldn't fix it up for you. Finally, saved as modern xlsx.
TaDaa!
Done. Any Q's, just shout.
Bookmarks