3

I've downloaded the California State University Final Budget Summary for last year from this website:

http://www.calstate.edu/budget/final-budget-summaries/documents/15-16-Final-Budget&Positions.xlsx

Most of the data is presented as a pivot table. I was curious to see the actual data that the pivot table is referencing. I looked at "Change PivotTable Data Source" and this is what's listed as the current source:

'\\Aardvark\bf\BUD'1516-Final-Budget'!$1:$1048576GET\Budget_Office-Deliverables\Final-Budget-&-Actual-Summaries\2015-16-Blue-Book\2015-16-Final-Budget\[1516.Final.Budget.Blue.Book.xlsx]Totals'!$A$1:$H$5922

By my reading, there is another .xlsx file that is being referenced called "1516.Final.Budget.Blue.Book.xlsx".

So my questions:

  • Is there a method for me to access the second .xlsx file directly?
  • Did they release this data in this method to specifically keep people from accessing the second .xlsx file?

I'm not looking to "hack the system" or gain access to information that's not publicly available. I am just interested in how this link works and, if possible, looking at the data structure that the PivotTable is referencing.

  • You can try this for yourself. Create an two excel files in your computer - with source data in one file and pivoted data in second file. Upload the second file to google drive or dropbox. Download the second file on to another directory from the Internet and see if the pivoted data is still intact and if it allows you to access the first file (that was not uploaded) . If this is possible, then you have the answer for your question – Prasanna Feb 28 '16 at 18:06
3

No, you can't access that other .xlsx file as it's sitting on their system.

You can however extract all the underlying data from the PivotTable very easily. First, turn on Grand Totals for both Rows AND Columns:

enter image description here

Then double-clicking on the Grand Total cell at the bottom right:

enter image description here

This will insert a new sheet into the workbook containing all 5921 underlying records.

| improve this answer | |

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.