Dynamic Offset Summarize Pivot Data

**matt4003** · 02-27-2012, 01:12 PM

Hello All,

I have been struggling with, what I thought was pretty straight forward, problem.

Basically, I have a large excel file with many many tabs, all with pivots that summarize similar data, and vary in number of rows. Lately I have been using a simple sumproduct equation to match values in the "volume" column with the minimum Market Share requirement. It works fine, but it is tiresome to update every tab all the time, I would like a more elegent way. I figured all I would need to do is a simple sumproduct statement with a dynamic offset embedded. But it didn't work.

I have attached a dummy data set with the pivot and how my file is generally structured...any thoughts??

Many thanks!
Matt

**rylo** · 02-28-2012, 01:23 AM

Hi

1) you will have to get rid of the circular reference. Start by moving the block of data from B20:C20 to E20
2) Change the formula to be

Please Login or Register  to view this content.

HTH

rylo

**matt4003** · 02-28-2012, 03:10 AM

Thanks Rylo!

That of course works. Is there anytype of formula which would allow me to keep the calculation cells below the pivot table? I ask because my current structure is like this. If not, its okay, just wondering if it is possible.

Many thanks!
Matt

**rylo** · 02-28-2012, 05:50 PM

Matt

If you can remove the text from column A from A21 downwards then you could do

Please Login or Register  to view this content.

For every additional text item in column A, then the -2 adjustment factor would have to negatively increased (ie -2, -3, -4....)

Does that help?

rylo