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

coxash123

New Member
Joined
Aug 16, 2016
Messages
37
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:eek:
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
It might help if you posted the code for the function ;)
 
Upvote 0
not sure I know what you mean?

12th11th
12thThis cell (D6) has the formula below (should read Ashley Watson)
11th

<tbody>
</tbody>


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

VResult HresultVHName
12th12th12th12thAshley Watson

<tbody>
</tbody>

the formula above is reading the chart below. It is placing the results into the table. Comparing verticle result with horizontal result.Ashley
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!!!
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top