0

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!

4
  • 1
    This can be done with formulas and be dynamic, but it isn't a pretty sight. You prepopulate a range with formula, each of which adds another qualifying value to the list. But it also needs to keep track of where you're looking in the source list and previous results. The formulas can be monsters. Sometimes it simplifies things using some helper columns. I don't have enough time (or coffee) to tackle this now, but maybe some other readers would be willing to give it a shot. You'll get a better response, though, if you expand the question with some sample data. – fixer1234 Apr 28 '19 at 11:38
  • You want it dynamic, but if you only need to update it once or twice a day, a simple workaround might be to use filters on the source data. Force the filter to recalculate and copy and paste the result. – fixer1234 Apr 28 '19 at 11:40
  • Should've made it more edit-friendly, sorry. I tried your code, and it outputs in F2 and nothing else. The reason for this being the line ` Worksheets("Sunday").Range("F" & Cells(Rows.Count, 5)` Here the "F" and the "5" should be the same column. But the 5 also refers to the current sheet. Try: Worksheets("Sunday").Range("F" & Worksheets("Sunday").Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).Row).Value = entry.Value – Christofer Weber Apr 29 '19 at 13:37
  • Edited with solution used. – OfcFriendly May 4 '19 at 6:04
3

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.

7
  • This gives me a manual representation of the data I'm looking for, and I thank you for that, but I need this to be dynamic and update automatically as the information is changed in the Staffing Pattern. Is there a way of making this method do this? – OfcFriendly Apr 28 '19 at 9:54
  • From the Data ribbon, hit Queries & Connections. Then right click that Query in the pane and choose Properties. You can set Refresh every 1 minute, and Refresh when opening the file. – Mike Honey Apr 28 '19 at 23:28
  • This worked perfectly. I then used the formula =IF('Back End'!A2<>"",'Back End'!A2,"") to call the data that was generated by the Power Query. This worked perfect, and gave me a dragable formula to expand my results and give me a list on another sheet. Thanks!!! – OfcFriendly Apr 29 '19 at 7:43
  • 1
    Well, it worked until I put it into practice. Apparently Excel doesn't really like refreshing 14 Power Queries all at the same time... I think I'm going to have do do this in VBA. – OfcFriendly Apr 29 '19 at 8:37
  • In VBA you can run the refresh using QueryTable.Refresh – Mike Honey May 1 '19 at 21:01
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.

![enter image description here

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
17
  • I understand using the functions on the sheet to call data from another sheet, but the issue I'm having there is that I want the list to condense, that way I don't have empty spaces in the list of names that outputs. That way each line has a name on it until all the spots on the staffing pattern have been processed. – OfcFriendly Apr 28 '19 at 9:40
  • That makes sense, and then this won't work. I guess you could have the first cell in the list search for the first valid entry, and the following rows do the same, but using the previous cell in the list as a starting point. That, or use VBA to populate the list. Or what Mike Honey said, I guess :) – Christofer Weber Apr 28 '19 at 9:46
  • How would I get excel to search for valid entries like you suggested? Is there a way to have it do that? – OfcFriendly Apr 28 '19 at 9:56
  • I'm sure there is, but I'm not exactly sure how to best write it in a function. It's easy enough with a Macro. But if you are using Macros, you could just use a macro to automatically update the PowerQuery, if that thing can't update automatically. I need to do a bit of testing. – Christofer Weber Apr 28 '19 at 10:09
  • I already have one macro written for determining if the text in a cell is bold or not, so macros are already enabled. If this requires a macro as well, I'm gonna need to do some more Visual Basic studying... I'm still relatively novice at this, but I'm a quick learner. – OfcFriendly Apr 28 '19 at 10:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.