Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-26-2011, 08:36 PM
Karen615 Karen615 is offline Pivot Table Windows 7 64bit Pivot Table Office 2010 64bit
Competent Performer
Pivot Table
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default Pivot Table

I have data (see attached) that I update every week. I have to track 20 regions of data and 5 different discrepancies.

I want to create a dynamic pivot table that I can update every week with the new data. Then I have to create charts as well.

The problem I'm having is that I don't just chart the entire 20 regions. I'll need a total of 25 charts. First chart would be region 2, 4, 10, 12 & 20 for discrepancy 1, then region 2, 4, 10, 12 & 20 for discrepancy 2 and so on to discrepancy 5. Then I choose 5 more regions, (example) 1, 5, 9, 17 & 19 for discrepancy 1 and so on to discrepancy 5. What is the best way to set this up?

Any help would be greatly appreciated


Thank you in advance
Reply With Quote
  #2  
Old 07-29-2011, 07:17 AM
Karen615 Karen615 is offline Pivot Table Windows 7 64bit Pivot Table Office 2010 64bit
Competent Performer
Pivot Table
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default

Ooops! It's looks as though I didn't attach the data. Any help would be greatly appreciated.
Attached Files
File Type: xls Test.xls (37.0 KB, 19 views)
Reply With Quote
  #3  
Old 07-29-2011, 07:53 AM
Kimberly Kimberly is offline Pivot Table Windows 7 64bit Pivot Table Office 2010 64bit
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

Something like this?
Attached Files
File Type: xls Test2.xls (44.5 KB, 23 views)
Reply With Quote
  #4  
Old 07-29-2011, 10:24 AM
Karen615 Karen615 is offline Pivot Table Windows 7 64bit Pivot Table Office 2010 64bit
Competent Performer
Pivot Table
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Smile

Thank you so much Kimberly! This is a great idea!
How do I make it dynamic so I can add data to the chart each week?
Thank you again for your help.
Have a great weekend, Karen
Reply With Quote
  #5  
Old 07-29-2011, 01:00 PM
Kimberly Kimberly is offline Pivot Table Windows 7 64bit Pivot Table Office 2010 64bit
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

Convert the data range into a table. To do so:
  1. Save your file as a .xlsx instead of .xls
  2. With the active cell in the data range, on the Home tab, click Format as Table button, and choose a table style.
  3. With the active cell in the table, go to the Design tab and notice what Excel named the table (probably Table1)
Now make your pivot table refer to the table:
  1. Click in the pivot table and on the Options tab, click Change Data Source
  2. Change the data source to the name of your table (Probably Table1)
But if you convert the range to a table before you make the pivot, it will automatically refer to the table.
Reply With Quote
  #6  
Old 08-03-2011, 10:46 AM
Karen615 Karen615 is offline Pivot Table Windows 7 64bit Pivot Table Office 2010 64bit
Competent Performer
Pivot Table
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Talking Thank You Kimberly

I'm so sorry I haven't been able to thank you for all your help Kimberly. You have been so helpful and I appreciate your time. I have one more question, I just have to take the time to post it. Thank you again! Have a great day!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
pivot table design 2 dmonroe Excel 2 11-15-2010 09:15 AM
Pivot Table Using subtotals in Pivot table formulas ninfanger Excel 3 10-23-2010 12:29 AM
Pivot Table Refresh tpcervelo Excel 0 09-14-2010 06:54 AM
pivot table source data hannu Excel 0 07-03-2010 04:54 AM
Pivot Table Pivot table problem swindon Excel 5 05-25-2010 02:05 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:25 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft