Cells not returning a value - when i know the formula is correct!!!

[coxash123]

I have this formula that is working in several other programmes that I have made, that are identical in nature. However, this one programme fails to return the value of my concatenation. The cell returns no errors. I have set the formulas to update automatically. I do believe this is an easy formatting issue. But I really have hit a wall with this one. It really bugs me that several of my other programmes work and yet this one does not.

=ConcatIf($S$7:$S$300,CONCATENATE($C6,$D5),$T$7:$T$300,";"&CHAR(10))

Any suggestions or support will be greatly appreciated,


Ashley

 

[Fluff]

It might help if you posted the code for the function

 

[coxash123]

not sure I know what you mean?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]12th[/TD]
[TD]11th[/TD]
[/TR]
[TR]
[TD]12th[/TD]
[TD]This cell (D6) has the formula below (should read Ashley Watson)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11th[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


=ConcatIf($S$7:$S$300,CONCATENATE($C6,$D5),$T$7:$T$300,","&CHAR(10))

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]VResult [/TD]
[TD]Hresult[/TD]
[TD]VH[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]12th[/TD]
[TD]12th[/TD]
[TD]12th12th[/TD]
[TD]Ashley Watson[/TD]
[/TR]
</tbody>[/TABLE]

the formula above is reading the chart below. It is placing the results into the table. Comparing verticle result with horizontal result.Ashley

 

[steve the fish]

What fluff means is that concatif is not a native excel function so must be a user defined function and so therefore has VBA code that defines it. To know if it should work for your situation the code needs to be provided.

 

[coxash123]

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, Optional Delimiter As String, Optional NoDuplicates As Boolean) As String

'Performance fix - only run if target sheet is active
Dim s As String
s = ActiveSheet.Name

If s <> "Target Grid" Then
Exit Function
End If


Application.ScreenUpdating = False
Application.EnableEvents = False

Dim i As Long, j As Long
With compareRange.Parent
Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
End With
If compareRange Is Nothing Then Exit Function
If stringsRange Is Nothing Then Set stringsRange = compareRange
Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, stringsRange.Column - compareRange.Column)

Dim hasItems As Boolean

hasItems = False

For i = 1 To compareRange.Rows.Count
For j = 1 To compareRange.Columns.Count
If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
hasItems = True
End If
End If
Next j
Next i


If hasItems Then
ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1) + ChrW(10) + ChrW(10)
Else
ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End If


Application.EnableEvents = True
Application.ScreenUpdating = True


End Function

 

[coxash123]

Well, i knew the solution was simple. Over tierd and looking at the same problem over and over again. I simply names the wrong sheet. Thank you Steve the fish!!!