I am trying to get excel to pull data from one sheet and list it on another sheet if certain criteria are met

Question

Essentially, here is what I'm wanting and don't know how to do it. I want a formula, or a function that will do the following.

IF A1<>"Vacant" AND B1="x" THEN add text from A1 to List1

The purpose of this is to then generate a list of names that can be copied to a separate worksheet. Here's what I'm doing. I have created a staffing pattern that has days off marked. I want a function that will compile a list of names for people that are off on a given day, denoted by column B through H. I also need it to disregard any names that are listed as "Vacant" because those spots need to stay, but aren't filled at the moment. Once I have that list compiled, I then need to have excel use that list to fill in a column on the shift roster for people that are regularly off.

After talking in the comments and coming a long way in my understanding of what I'm trying to do, I still am at an impasse.

I don't know what I'm doing wrong. I tried changing the references in the code I was given, but it's not giving me any output, and I'm not fluent enough in VBA to know what I'm doing wrong. Here is the code I'm using:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim nameRange As Range, entry As Range

Set nameRange = Worksheets("Staffing Pattern").Range(Range("A7"), Range("A21").End(xlUp))

If Not Application.Intersect(Range(Target.Address), Range("A:H")) Is Nothing Then
    Worksheets("Sunday").Range("F81:F" & Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Row).Value = ""
    For Each entry In nameRange
        If UCase(entry.Value) <> "VACANT" And UCase(entry.Offset(0, 1).Value) = "X" Then
        Worksheets("Sunday").Range("F" & Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Row).Value = entry.Value
        End If
    Next entry
End If
End Sub

EDIT I ended up using a combination of both of the answers provided with a little google ingenuity. I used the Power Queries to do exactly what I wanted to do, then used VBA to write macros and linked them to buttons on the pages. The macros only refresh two of the Power Queries at a time, so the system won't crash, and there is a button on each shift roster. Thanks for everyone's help in solving this!

Code I used for the macros:

Sub Macro1()
'
' Macro1 Macro
' Refresh Sunday
'
' Keyboard Shortcut: Ctrl+s
'
Dim con As WorkbookConnection
Dim Cname As String

For Each con In ActiveWorkbook.Connections
    If Left(con.Name, 11) = "Query - Sun" Then
    Cname = con.Name
        With ActiveWorkbook.Connections(Cname).OLEDBConnection
            .BackgroundQuery = False  'or true, up to you
            .Refresh
        End With
    End If
Next
End Sub

Each macro was then changed to apply to each day of the week, and a button was linked to it. It works perfectly!

Answer 1

I would use "Power Query": select your input range, go to the Data ribbon and choose From Table/Range. Then use the Filter buttons on each column header to apply your criteria:

• Column1 ... Text Filters ... Does not equal ... Vacant
• Column2 ... Text Filters ... Equals ... x

Then right-click Column2 and choose Remove Columns. Then hit the Close & Load button to generate a new table with the result, on a new Worksheet. You can tweak the Close & Load options on the way through.

In the future just hit the Refresh button on the Data ribbon to refresh the output table.

Answer 2

You can't really have a function adding something to a list, as a function only dictates what is to be shown in that specific cell. What you can do, is populate the list with functions so that it can fill itself. Or use a macro. It's a lot easier to make exactly what you want with some visual representation, but you can write the function you described as:

=IF(A1<>"Vacant",IF(B1="x",A1,""),"")

VBA stuff:

First off, when you want macros to run automatically, you can put them in the worksheet. Just right click the sheet to get there, or just select the sheet in the VBA editor.

Worksheet_Change is a useful function to run a macro each time something changes. Usually accompanied by a range restriction that looks something like:

If Not Application.Intersect(Range(Target.Address), Range("A2:D5")) Is Nothing Then

This to have it run only when changes occur in a range where we want to check.

You could do something like:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim nameRange As Range, entry As Range

Set nameRange = ActiveSheet.Range(Range("A2"), Range("A65000").End(xlUp))

If Not Application.Intersect(Range(Target.Address), Range("A:B")) Is Nothing Then
    Range("E2:E" & Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Row).Value = ""
    For Each entry In nameRange
        If UCase(entry.Value) <> "VACANT" And UCase(entry.Offset(0, 1).Value) = "X" Then
        Range("E" & Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Row).Value = entry.Value
        End If
    Next entry
End If
End Sub

To populate Column E with values from column A.

The downside of this method is that we delete and write the list every time there is an update. So if we have a very long list, it could get pretty slow.

You can use the same approach to update a Power Query, but using the line:

ThisWorkbook.Worksheets("Sheet2").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False

With Sheet2obviously being the sheet with the PQ.

Edit

So, I did some adjustments for editing. Could probably do more, but it kind of depends on how you want to utilize it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nameRange As Range, entry As Range, outCol As String, oCN As Long, outSheet As Worksheet

Set nameRange = Worksheets("Staffing Pattern").Range("A7:A21")
Set outSheet = Worksheets("Sunday")
outCol = "F"


oCN = Columns(outCol).Column
If Not Application.Intersect(Range(Target.Address), Range("A:H")) Is Nothing Then
    outSheet.Range(outCol & "2:" & outCol & outSheet.Cells(Rows.Count, oCN).End(xlUp).Offset(1, 0).Row).Value = ""
    For Each entry In nameRange
        If UCase(entry.Value) <> "VACANT" And UCase(entry.Offset(0, 1).Value) = "X" Then
        outSheet.Range(outCol & outSheet.Cells(Rows.Count, oCN).End(xlUp).Offset(1, 0).Row).Value = entry.Value
        End If
    Next entry
End If
End Sub

Currently, this will clear the entire output Column in the output sheet. Then fill it from output column row 2 and down, with values from nameRange that aren't "vacant" and has got an adjacent X.

If you want to apply this to several positions, you can convert every option you want to change into a variable, and then call it instead. Mainly to not repeat all the code over and over.

What i mean is something like this: In the sheet with the list being edited, you put something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range(Target.Address), Range("A:H")) Is Nothing Then
    Call writeList("Staffing Pattern", "A7:A21", "Sunday", "F")
    Call writeList("Staffing Pattern", "C5:C40", "Sunday", "H")
    Call writeList("Staffing Pattern", "A7:A21", "Saturday", "F")
End If
End Sub

And then in a Module, you put something like this:

Public Sub writeList(ByVal inSheet As String, nameRange As String, outSheet As String, outCol As String)
Dim entry As Range, oCN As Long, outS As Worksheet, nRange As Range

Set nRange = Worksheets(inSheet).Range(nameRange)
Set outS = Worksheets(outSheet)
oCN = Columns(outCol).Column

    outS.Range(outCol & "2:" & outCol & outS.Cells(Rows.Count, oCN).End(xlUp).Offset(1, 0).Row).Value = ""
    For Each entry In nRange
        If UCase(entry.Value) <> "VACANT" And UCase(entry.Offset(0, 1).Value) = "X" Then
        outS.Range(outCol & outS.Cells(Rows.Count, oCN).End(xlUp).Offset(1, 0).Row).Value = entry.Value
        End If
    Next entry

End Sub

You could just put them under each other in the Sheet code if you like, having it in a Module isn't a requirement. You can put it where ever since it's public.

You can also split the sheet code, so that you don't have to redraw lists that aren't affected by the change. Either with multiple IF's or with else if

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range(Target.Address), Range("A:B")) Is Nothing Then
    Call writeList("Staffing Pattern", "A7:A21", "Sunday", "F")
    Call writeList("Staffing Pattern", "A7:A21", "Saturday", "F")

ElseIf Not Application.Intersect(Range(Target.Address), Range("C:D")) Is Nothing Then
    Call writeList("Staffing Pattern", "C5:C40", "Sunday", "H")

End If
End Sub