Select multiple items in pivot list based on selection in another cells drop down list

[JesusH]

So, i have an invoice i am working out. I am using a pivot to calculate the "billable hours" from a timepunch data dump out of our pay system. The employees are organized by department, and display name, time in, time out, and hours.

On another sheet i have an invoice. On this invoice is a drop down list containing a list of our clients. When you select a client, the invoice populates with that clients data (address, etc).

What i would like, though, is when you select the client, the pivot will automatically display the departments that work for that client (since we parse our labor departments by which client we bill their labor to). This would make invoicing rather simple, as the manager would then just select a client from a drop down list, and print the invoicing (the invoice includes the invoice, as well as the punch information as a "labor detail" backup).

So, i guess the help i need is in understanding how i can reference a list of departments (which vary in number from client to client) that are tied to our clients as a conditional filtering argument in the pivot table.

 

[Jerry Sullivan]

Here is a function that you could use for that.

You can call the function using two parameters:
pvtField - the PivotField object for your Department
vItems - a Variant Array of Departments you want to make visible.

Public Function Filter_PivotField(pvtField As PivotField, _
        vItems As Variant)
'---Filters the PivotField to make stored vItems Visible
    Dim sItem As String, bTemp As Boolean, i As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    If Not (IsArray(vItems)) Then
         vItems = Array(vItems)
    End If
 
    With pvtField
        .Parent.ManualUpdate = True
        If .Orientation = xlPageField Then .EnableMultiplePageItems = True
        If vItems(0) = "(All)" Then
            For i = 1 To .PivotItems.Count
                If Not .PivotItems(i).Visible Then _
                    .PivotItems(i).Visible = True
            Next i
        Else
            For i = LBound(vItems) To UBound(vItems)
                bTemp = Not (IsError(.PivotItems(vItems(i)).Visible))
                If bTemp Then
                    sItem = .PivotItems(vItems(i))
                    Exit For
                End If
            Next i
            If sItem = "" Then
                MsgBox "None of filter list items found."
                GoTo CleanUp
            End If
            .PivotItems(sItem).Visible = True
            For i = 1 To .PivotItems.Count
                If IsError(Application.Match(.PivotItems(i), _
                    vItems, 0)) = .PivotItems(i).Visible Then
                    .PivotItems(i).Visible = Not (.PivotItems(i).Visible)
                End If
            Next i
        End If
    End With
    
CleanUp:
    pvtField.Parent.ManualUpdate = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Function

Just ask if you want some help incorporating this into your process.

 

[JesusH]

Just ask if you want some help incorporating this into your process.

It looks like this is making an initial selection box?

I am not strong with VBA, but i have gotten my feet wet. And i have programming in my background, but it was mostly stuff like basic and pascal in the late 80's/early 90's. What i am saying is, i am competent to deploy this stuff with a little help...which i thank you (and everyone) for being willing to offer.

Yeah, i would love some help getting this deployed. It is a function that would be repurposed quite a bit in other workbooks i make in the future as i set up the business processes here.

Basically, the pivot provides all the needed data. So if i could just set up a series of if's, pointing departments to clients, it would be perfect.

 

[JesusH]

I don't think i have been very clear, which is unusual for me.

I have an invoice, and under the "To" section there is a drop down list in the cell for the user to select the client. It will currently also vlookup to a list that populates address info. Nothing fancy. What i want it to do is tie in to the pivot table. Is it possible to make this cell Data Validation list tie in to the pivot table? Or does it need to have an additional input that would control the entire process? And when it ties into the pivot, it needs to actually alter the pivot table so it will be ready to format for printing as an invoice backup.

From there, how would i make that happen, considering that there are currently about 12 clients, but growth happens suddenly and quickly? And that each client pulls in multiple departments (covering multiple employee levels, etc)?

I would guess a HUGE series of nested if/or's could do it But i think VBA might have a more eloquent solution.

 

[Jerry Sullivan]

That explanation helps- I was misunderstanding that you wanted to filter the PivotTable base on a list of Departments. Now it's clear you want to filter the PT for a single client, and that the result of that will be to show only the departments associated with the client.

No you don't need any nested if/or formulas- the PivotTable filter can do all that work.

Just a few clarifications, so I can suggest some code to try....
1. What area of the Pivot Table is your Client Field? Check the PivotTable Field List Pane- it's probably in the Report Filter or Row Label area.

2. If the Client field isn't in the Report Filter area, are you able to move it there? This will simplify the PivotTable filtering.

3. What's the Sheet Name and Cell address of the Data Validation Cell to select Client?

4. What's the Sheet Name and PivotTable Name for the PivotTable to be filtered?

 

[JesusH]

That explanation helps- I was misunderstanding that you wanted to filter the PivotTable base on a list of Departments. Now it's clear you want to filter the PT for a single client, and that the result of that will be to show only the departments associated with the client.

No you don't need any nested if/or formulas- the PivotTable filter can do all that work.

Just a few clarifications, so I can suggest some code to try....
1. What area of the Pivot Table is your Client Field? Check the PivotTable Field List Pane- it's probably in the Report Filter or Row Label area. There isn't a client field in the pivot. The pivot displays only department numbers. Each client has between 2 and 10 different departments assigned to it. To make matters more difficult, some departments are assigned to multiple clients (with the labor hours being allocated). For example, 2 departments work across 5 different companies. So we allocate their labor at a 20% rate to each company. I have the pivot set with a calculated field to display the labor for these departments at only the 20% rate (so it will appear to be allocated automatically when those departments are filtered)

2. If the Client field isn't in the Report Filter area, are you able to move it there? This will simplify the PivotTable filtering.You know, i suppose I COULD put in a simple vlookup formula into a far right column on the data sheet. However the difficulty would be that most departments have multiple client assignments, with allocated distributions related to each. If you have a solution to this that I am not seeing, that would be outstanding....but the next phase of this invoicing process is to include another tab that will bring in data based on other revenue channels from the same client (we offer more than just labor, and some most clients get billed for more). The labor section is just the hardest part to do manually, so i started with it as the low hanging fruit. I was hoping to make it to where once you selected the client, excel would handle the pivoting of departments as well as the fetching of this additional invoicing data (we get it from a dump of data from another system)

3. What's the Sheet Name and Cell address of the Data Validation Cell to select Client?The sheet name is "Invoice", and the cell address is B11

4. What's the Sheet Name and PivotTable Name for the PivotTable to be filtered?Labor Hours Detail for both

My apologies. This is payroll week, and i am busybusybusy. I provided answers in line above, bolded for ease of reading.

I would suppose that it may be possible to do this another way by scrapping the pivot idea, and instead making a more elaborate workbook employing a whole bunch of sumif and sumproduct. My issue with that (which was my first idea) is that i do not know how to take a list with multiple records for the same employee, with several employees on the list, and create a list of employees from that list (you know...like have it return a list of names that are in the data table). If i could do that easily, making the invoice would be an easier process i think by just setting up the sheet using sumif, etc.

 

[Jerry Sullivan]

Okay- that's becoming a little clearer.

If you don't already have it, you'll need some means to map a selected client to a list of departments.

You'll also need a means to lookup or calculate how many clients a department serves so your calculated field knows whether to allocate the cost by 20% per client or 50% per client.

I understand the demands of payroll week, so no rush for you to respond.

 

[JesusH]

Okay- that's becoming a little clearer.

If you don't already have it, you'll need some means to map a selected client to a list of departments.

You'll also need a means to lookup or calculate how many clients a department serves so your calculated field knows whether to allocate the cost by 20% per client or 50% per client.

I understand the demands of payroll week, so no rush for you to respond.

My thoughts around this would be to create a table that has the client in the far left column, with all departments listed out to the right of that column. While I know I could "vlookup" the data, i am unsure how to include this lookup function in the scripting. Or if this would even provide the data in a way it is needed.

Currently i have the allocated departments set to only display labor hours as it pertains to the allocated set. I included a calculated field (using if/or) to determine if it was in need of allocation, and if so if it would need to be a 20% or a 50% allocation. Of course, ideally it would not allocate a flat percentage, but rather flex based on receipts/customer contacts. But this elephant needs to be eaten one bite at a time (and the variance from the 20%-50% allocations is normally under 1%, so it is more of an academic matter) and I think that if i get the core infrastructure established, I would be able to make modifications based on a billable receipts data dump in another tab. Or even an "allocation override" data input field to establish the allocation % of that department for that particular invoice.

 

[JesusH]

On further thought, it may be easier to use a Sumif function to identify departments affiliation with a client. it seems to be the most straightforward method, anyway.

 

[Jerry Sullivan]

On further thought, it may be easier to use a Sumif function to identify departments affiliation with a client. it seems to be the most straightforward method, anyway.

There are probably many ways this could be done whether formula-based or Pivot.

Perhaps you could mock up a small screen shot (with fake data) showing:

What you currently have, or the easiest way for you to organize your existing data.

What your desired result report would look like ideally.

I won't be able to look at this until tonight, but perhaps someone else will have a suggestion if you can provide those two screen shots.