# Thread: Concatenate Ifs Function (2002)

1. ## Concatenate Ifs Function (2002)

I am making up a summary sheet based on the contents of other worksheets.
For example, I have worksheets named A, B, C and D in whose cells contain either the letter "H", "S" or are blank.
In cell A1 of my summary sheet, I want to know if there is the letter "H" in cell A1 of worksheets A, B, C and D, and if so, put the appropriate letter (worksheet name) in cell A1.
So if A, B and D each had the letter "H" in cell A1, my summary sheet cell A1 would contain "A, B, D".
I can get as far as making a function that'll work on a single IF, after that I'm quite lost. Or would it be simpler to run a macro that runs when the summary sheet is selected?

TIA.

2. ## Re: Concatenate Ifs Function (2002)

If you're willing to do without the commas and spaces, this relatively simple formula would do the job:
<code>
=IF(A!A1="H","A","")&IF(B!A1="H","B","")&IF('C'!A1 ="H","C","")&IF(D!A1="H","D","")
</code>
If you need the commas and spaces, a VBA function is probably easier.

3. ## Re: Concatenate Ifs Function (2002)

Here is a function you can use:
<code>
Function ListSheetsWithH(oCell As Range) As String
Dim strResult As String
strResult = ", A"
End If
strResult = strResult & ", B"
End If
strResult = strResult & ", C"
End If
strResult = strResult & ", D"
End If
If Not strResult = "" Then
strResult = Mid(strResult, 3)
End If
ListSheetsWithH = strResult
End Function
</code>
Place the following formula in A1 on the summary sheet:
<code>
=ListSheetsWithH(A1)
</code>
You can fill this down (or right) if needed.

4. ## Re: Concatenate Ifs Function (2002)

Excellent - thank you!

5. ## Re: Concatenate Ifs Function (2002)

Warning: the function "as is" doesn't update itself automatically. You can add a line

Application.Volatile

at the beginning of the function to make it update each time the workbook is recalculated.

6. ## Re: Concatenate Ifs Function (2002)

The modification of Hans' function below will update automatically and is more versitile in that it can be used to check any four cells and the sheets don't have to be named A,B,C. and D:

<code>
Function ListSheetsWithH(oCell1 As Range, oCell2 As Range, oCell3 As Range, oCell4 As Range) As String
Dim strResult As String
If oCell1 = "H" Then
strResult = ", " & oCell1.Parent.Name
End If
If oCell2 = "H" Then
strResult = strResult & ", " & oCell2.Parent.Name
End If
If oCell3 = "H" Then
strResult = strResult & ", " & oCell3.Parent.Name
End If
If oCell4 = "H" Then
strResult = strResult & ", " & oCell4.Parent.Name
End If
If Not strResult = "" Then
strResult = Mid(strResult, 3)
End If
ListSheetsWithH = strResult
End Function
</code>

It is called like this:

<code>
=ListSheetsWithH(A!A1,B!A1,'C'!A1,D!A1)
</code>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•