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!