Pivot Chart Question

**lasadajohnson** · 07-31-2009, 02:40 PM

I am trying to make an ongoing pivot chart for my supervisor that measures the time consumption of a specific project being carried out by various workers in the office.

The raw data that I'm working with only has the date (column A) and time consumed (column B). The problem is when i select everything to add to the pivot the pivot chart calculates the frequency of entries on each date. (eg: 1hr, 2hr, 3hr on june 4th = 3) I want the chart to display 1+2+3 = 6 for on june 4th. I get what I want when I highlight the data that I already have. The problem is when I (or other employees) add new data to the raw data it does not update the pivot (even if I refresh)! This is because it wasn't initially highlighted. If I highlight column A and B infinitely or even 1 box of blank data the chart calculates frequency instead. The point of this chart is so that people can log data into the raw data and have the pivot chart update itself! How can i fix this damn auto configuring excel pivot chart from calculating frequency!? Or at least how can I adjust a pivot chart to accommodate new data without having to redo the entire thing?

Thanks!

**DonkeyOte** · 07-31-2009, 02:45 PM

Might me an idea to post a sample... I'm not sure I follow... it sounds as though perhaps you have your data field set to Count rather than Sum ... re: range expansion, perhaps best to use a Dynamic Named Range as the source

**lasadajohnson** · 07-31-2009, 03:06 PM

Originally Posted by DonkeyOte

Might me an idea to post a sample... I'm not sure I follow... it sounds as though perhaps you have your data field set to Count rather than Sum ... re: range expansion, perhaps best to use a Dynamic Named Range as the source

Okay, I added an example with the two problematic pivot tables juxtaposed with each other.

**DonkeyOte** · 07-31-2009, 03:19 PM

As I say - it's probably a good idea to use a Dynamic Named Range.

You can create a Name via Name Manager (or ALT + I -> N -> D) as follows:

Name: _PTSource
RefersTo: =Sheet1!$A$1:INDEX(Sheet1!$B:$B,MATCH(9.99999999999999E+307,Sheet1!$B:$B))

Modify your PT such that source range is set to be: _PTSource

If you now add a new transaction at the end of your data set (ie date in A and value in B) and now refresh the PT you should find it updates accordingly encompassing the new values.

**lasadajohnson** · 07-31-2009, 03:58 PM

THANK YOU SO MUCH, it worked!!! You saved my otherwise horrific day!!

**DonkeyOte** · 07-31-2009, 04:01 PM

No problem, please remember to mark thread as Solved (see How To ? / FAQ if unsure)

Also, on an aside, try to avoid quoting entire messages - limit the quote to those specific points worthy of referencing... we only ask this because else the board can become a tad cluttered. TIA for your ongoing co-operation.