Re: Pivot Tables,Sliders, blank rows and expanding source data help

[Jaime1234]

Hi All,

I'm fairly new to creating complex Excel charts and Pivot Tables so would be grateful for any advice on the following problem:

I am creating an Excel Dashboard to report on various Key Performance Indicators held in a SQL database.

A separate programme exports the data on each KPI from the database to an Excel Sheet. These Excel sheets are overwritten each day so other than the source data cannot contain any bespoke formatting.

Over time the data in each of these Source Data Sheets will grow.

I was intending to cerate a number of Intermediate Data workbooks which link to the Source Data workbooks but in which I can perform calculation and format the data perform these workbooks then act as a the source for the Pivot Table.

My problem is when linking the Intermediate Workbooks to the Source Workbooks I need to take account in the growth of data so I am referencing cells A1:B5000 even though there is only currently data contained in Cells A1:B10.

However when I then create my pivot table and filter this using sliders on Year, Month and Quarters. I then get a list of all date years going back to 1900. Which is something I don't want.

I have tried hiding blank rows but of course while this does not display the blank rows they still form part of the calculation.

Is there another way to either take account of the growth in data or to format the Pivot Table and or Sliders to only report on non blank rows.

Thanks
JK

PivotTable.xlsx

[JosephP]

perhaps you could use an external query as the data source for the pivot table rather than using a one range consolidation-that will permit you to specify a sheet as the data source, which will be treated as a table and return whatever data is in it

[Jaime1234]

Hi Joseph,
Thanks for your reply. Unfortunately I have two issues with doing it that way.

Firstly I have about 20 Reports that are run so to create the Pivot I am using the Multiple Consolidation Ranges. I assume you can only select one data source so would I have to copy the data from all sheets into one?

Secondly I have tried selecting one of the sheets as a data source but I get an error cannot read file. Its a .xlsx file is this not a valid data source.

I have never used an external data source with a pivot table before (this is my first time using pivot tables).

Thanks
JK

[JosephP]

a pivot table can certainly use an xlsx file as a data source-I don't know what your issue is there

I've gotta say this sounds really inefficient as a setup-are you able to change any of it?