CODEWITHSUNDEEP
excelexcel-formulavba
duger99
duger99

Reputation: 13

Copy specific column header to new sheet if it meets certain criteria

I am trying to create a new row in Sheet 2 that consists of values from row 1 in Sheet 1.

Sheet 1 has: 

   A    B     C     D       E      F
1 Bob  Sam   Ken  Allen   Henry   Ed
2 Yes  No    Yes  No      Yes     No

I want a formula to create row 1 in Sheet 2 with only those values where row 2 = "Yes".

So Sheet 2 would look like:

      A    B     C     D       E      F
 1    Bob  Ken  Henry

This would dynamically update as the data in Sheet one is updated.

Upvotes: 1

Views: 340

Answers (2)

BruceWayne
BruceWayne

Reputation: 23285

Using this formula will work (enter in A1 on Sheet 2, and drag right)

=IF(Sheet1!A$2:$F$2="Yes",Sheet1!A$1:$F$1,"") Note: Enter as an array by pressing CTRL+SHIFT+ENTER.

Edit: I noticed that the formula above will leave a blank in between columns that have a "no" equivalent. I'm trying to get a formula that removes the gap, but still allows a drag right. The following does this except I can't figure out why "Bob", in A1, isn't being returned. It starts with "Sam". (Also enter as array)

=INDEX(Sheet1!$A1:$F1,SMALL(IF(Sheet1!$A2:$F2="Yes",COLUMN(Sheet1!$A1:$F1)-COLUMN(Sheet1!$A1)+1),COLUMNS(Sheet1!$A1:B1)))

Any ideas? I suspect it's something to do with my use of Small(). If you change it to Large(), the order that it returns is reversed

Here's the table:

enter image description here

and here's what Sheet2 looks like when using that array formula:

enter image description here ...What About Bob?!

(The #num is just because there's no more matches. Just throw an IfError([array formula], "") around the whole thing to remove this).

Edit2: Got it! Use this formula, entered as an array formula:

=IFERROR(INDEX(Sheet1!$A1:$F1,SMALL(IF(Sheet1!$A2:$F2="Yes",COLUMN(Sheet1!$A1:$F1)-COLUMN(Sheet1!$A1)+1),COLUMNS(Sheet1!$A1:A1))),"")

(The final range should have been $A1:A1, not $A1:B1)

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

For VBA, because it was in the tags, put this in the worksheet events where the data is:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("2:2")) Is Nothing Then
        Dim ws As Worksheet
        Dim rng As Range

        Set ws = Worksheets("Sheet5")'change to what ever sheet your want

        Dim strArr() As Variant
        ReDim strArr(0)
        With Target.Parent
            For Each rng In .Range(.Cells(1, 1), .Cells(1, 1).End(xlToRight))
                If rng.Offset(1) = "Yes" Then
                    strArr(UBound(strArr)) = rng
                    ReDim Preserve strArr(UBound(strArr) + 1) As Variant

                End If
            Next rng
        End With
        If ubound(strArr) > 0 then
            ReDim Preserve strArr(UBound(strArr) - 1) As Variant
        End If
        ws.Range("A1").Resize(, UBound(strArr) + 1).Value = strArr
    End If
End Sub

Upvotes: 0

Related Questions