Results 1 to 6 of 6
  1. #1
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenate Ifs Function (2002)

    Quick help please!

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenate Ifs Function (2002)

    Here is a function you can use:
    <code>
    Function ListSheetsWithH(oCell As Range) As String
    Dim strAddress As String
    Dim strResult As String
    strAddress = oCell.Address(False, False)
    If Worksheets("A").Range(strAddress) = "H" Then
    strResult = ", A"
    End If
    If Worksheets("B").Range(strAddress) = "H" Then
    strResult = strResult & ", B"
    End If
    If Worksheets("C").Range(strAddress) = "H" Then
    strResult = strResult & ", C"
    End If
    If Worksheets("D").Range(strAddress) = "H" Then
    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. #4
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate Ifs Function (2002)

    Excellent - thank you!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>
    Legare Coleman

Posting Permissions

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