BC, this one's for you... :-)
Situation: You have an Excel solution that auto generates Graphs and / or Charts based on a specific set of data that changes over time. Instead of manually re-selecting a new Data Source for your Pivot Table(s) and / or Graph(s) each time your Raw Data changes (i.e. appending Q2 data to Q1, etc.), check out the information below for a dynamic solution...
- Excel Sheet Name = "DataTab"
- Column Headers Accross the top beginning with A1
- Subsequent Data lies beneath each Header
- Assuming your data is standardized and consistent over time
- Create a Named Range called "DataRange" and paste in the following formula...
=OFFSET('DataTab'!$A$1,0,0, COUNTA('DataTab'!$A:$A), COUNTA('DataTab'!$1:$1))
- Assign the above Named Range ("DataRange") to your Pivot Table and/or Graph as it's Data Source
- Refresh your Table(s) / Graph(s) - Can be done manually, via Table Settings, or with VBA
When you paste in future data that may be different from your original data, your Graph(s) and / or Chart(s) will now be based on a dynamically growing or shrinking dataset that matches your "evolved" Data Source.
