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
Bookmarks