Puzzling Pivot (1 Viewer)

[cgadmin]

Hi there, the charity I work for (I'm the IT manager) for uses an Excel workbook to process some data that is used for payroll production.

This workbook was produced by a third party who is (surprise, surprise) no longer contactable and didn't leave instructions on how the workbook functions.

I decided to take a look at it to see if I could document it myself.

Long story short the "high-level" workflow is essentially:

1. Paste some data into it from a report from our rota system
2. Hit "refresh connections"
3. Hit a "Go" button
4. A little bit of VBA runs and the output is produced in a new tab

The VBA was the first thing I looked at and it is fairly straightforward but doesn't do any of the number crunching and doesn't contain any logic - instead it just refreshes the queries and then copies the final results into the desired output format.

This is the code that refreshes the queries:

'Refresh queries
    For i = 1 To ThisWorkbook.Connections.Count
        ThisWorkbook.Connections(i).Refresh
    Next i

Within the connections a query is defined that pulls the data from the tab its pasted into. The steps defined in the query appear to do some tidying up of the data, manipulating formats etc.

There is also a tab with pivot table which draws its data from the query. When the VBA code runs it essentially copies the pivot table data out into a "flat" normal table in a different tab and formats it.

There is some logic & calculation happening in all of this but for the life of me I can't find it.

The raw data tab contains rows for different appointments, their duration and their type. For appointments of type "none selected" we want to sum the durations for the final output. For appointments of type "sleepover" we want to ignore the duration and instead count the number of appointments.

This is what the raw data looks like:

After refreshing the queries the output pivot looks like:

So the sleepovers are being excluded from the sum of durations and the appointments marked as "sleepover" are being counted. It is working as intended but I just don't know how!

Where in the workbook would you define what gets summed and what gets counted in this manner?

I've tried looking at both the pivot table and the queries but I nothing leapt out at me. I wondered about "Calculated Fields" in the pivot - but this is greyed out in the output pivot.

Any tips on where I should look to find this configuration? Should I be digging around in the pivot or in the queries & connections?

Thanks in advance!

 

[Uncle Gizmo]

What with the holidays and everything, I think your post has been missed! I thought I'd take this opportunity to bump your post up the list and hope that someone who can help you reads it.

 

[oleronesoftwares]

Are there any hidden columns in the sheets?

 

[bastanu]

I would say take a close look at the pivot table. Can you post a small sample with some dummy records?
Cheers,