not sure if its no longer a recognised formula but I have an old spreadsheet where I am using a concatenate if formula
but all I keep getting is the #NAME error so I am assuming that this is no longer a compatible formula can any one confirm and if so what can I use to replace this and get the same result many thanks in advance
Re: concatenateif formula not working ?
I've never know of its existence. Which version of Excel are you running?
Re: concatenateif formula not working ?
EDIT:
My error, I didn't notice the "IF" part, maybe that's a custom function someone created for you in VBA?
Re: concatenateif formula not working ?
Looks to me like it is a formula that uses this UDF, which was created by Hans a few years ago, see:
http://www.eileenslounge.com/viewtopic.php?f=27&t=12298
There is even an extension of the UDF there for ConcatenateIfs which works like SUMIFS or COUNTIFS!
This is the UDF
Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim i As Long
Dim strResult As String
On Error GoTo ErrHandler
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
End If
Next i
If strResult <> "" Then
strResult = Mid(strResult, Len(Separator) + 1)
End If
ConcatenateIf = strResult
Exit Function
ErrHandler:
ConcatenateIf = CVErr(xlErrValue)
End FunctionRe: concatenateif formula not working ?
this has worked a treat thank you so very much this has now saved me hours of work thank you
Re: concatenateif formula not working ?
You're welcome.
Don’t have an account yet? Register yourself now and be a part of our community!