How do I tie a column to a pivot table?

**ggunter** · 05-22-2013, 10:37 AM

I ran across an Excel file that does something I need to do but the creator is no longer with the company and I can't find anyone that knows how they did this. I'm searching online but have not yet been able to find anything.

In the attached file the "Weekly" worksheet contains a pivot table. Next to the table is a column labeled "Points". The "Points" column does not appear to be part of the pivot table (at least the PivotTable Tools option at the top disappears when you click in the column) but it expands and collapses with the table.

I can't find an Excel option or any code that would account for this behavior. I need to duplicate this functionality for some post-calculated data that I need to display.

Any help would be greatly appreciated.z_ExampleFile.xlsx

**FDibbins** · 05-22-2013, 11:11 AM

The 1st part of the formula is testing to see if certains cells contain data. if they do not, it returns a NULL value (empty cell)...

=IF(($A6="")*AND($B6=""),"",SUM($Q6:$AA6))
I would have done it slightly differently, but that works too...
=IF(AND($A6="",$B6=""),"",SUM($Q6:$AA6))

**ggunter** · 05-22-2013, 11:23 AM

Thank you for the reply.

The column that contains that formula is for calculating if the Agent gets a point for that metric.

However, I need to know what makes the "Points" column expand and collapse with the Pivot Table itself. The "Points" column doesn't appear to be part of the Pivot Table so how does one get the column to act as if it is.

**FDibbins** · 05-22-2013, 11:36 AM

I just told you how it does that. If there is no value in A or B of that row, it shows nothing. it is not "expanding or collapsing", it is just showing - or not showing - a value depending on if those 2 cells contain data. They will contain data if the PT extends or shrinks.

to show you what I mean, on a clear worksheet, enter this...
A1 = 10
B1 = 20
C1 = if(and(A1="",B1=""),"",A1+B1

Now, delete either 10 or 20 and see what happens

**ggunter** · 05-22-2013, 11:36 AM

So Sorry!!

I found the problem. The pivot table list the Manager at the top and again at the bottom. However, the bottom adds the word "Totals" to the name. I had to strip that out and that is causing the problem with my column.