gunsofbrixton001
New Member
- Joined
- Jun 13, 2011
- Messages
- 1
Hello,
I've been developing a front end report based on a MSQuery data feed, populating a Pivot Table. I have calculations in columns off to the end of the Pivot Table that generate custom metrics (from the table). Based on the previous day's data, the pivot table changes in size (number of rows). I want to find a way (VBA or otherwise) that will autofill calculations downwards based on whether or not a pivot table is populating the same row.
I was thinking something close to a dynamic range would accomplish this, but I don't have the insight to do it.
Thus far, my solution has simply been to include a conditional 'If' within each forumla. For instance, I would include "=IF(F11="","",SUM(G11,H11,I11)/86400)" for each calculation and fill down the max number of rows that I thought the report would ever necessitate. This however, makes the workbook about 34megs in size, so I'm looking for an alternative.
The report is also sitting on a SharePoint so I want to have the size as small as possible. Having a dynamic reference of calculations that would autofill down based on whether or not the Pivot Table filled its respective row would solution this.
I've been developing a front end report based on a MSQuery data feed, populating a Pivot Table. I have calculations in columns off to the end of the Pivot Table that generate custom metrics (from the table). Based on the previous day's data, the pivot table changes in size (number of rows). I want to find a way (VBA or otherwise) that will autofill calculations downwards based on whether or not a pivot table is populating the same row.
I was thinking something close to a dynamic range would accomplish this, but I don't have the insight to do it.
Thus far, my solution has simply been to include a conditional 'If' within each forumla. For instance, I would include "=IF(F11="","",SUM(G11,H11,I11)/86400)" for each calculation and fill down the max number of rows that I thought the report would ever necessitate. This however, makes the workbook about 34megs in size, so I'm looking for an alternative.
The report is also sitting on a SharePoint so I want to have the size as small as possible. Having a dynamic reference of calculations that would autofill down based on whether or not the Pivot Table filled its respective row would solution this.
