Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Unhappy Dynamic Datasource Code for Pivot Tables

Can someone please help me out. I am trying to build a pivot table that will auto update and within the table do groupings by week. My source data headings are on Row 1 and data covers columns A-H. One of the sticking points I have is that their are some blanks in columns C,R,G,H. Can anyone tell me how to get this to work?
  #2   Report Post  
Senior Member
 
Location: Hyderabad
Posts: 237
Thumbs up

Quote:
Originally Posted by mztexas View Post
Can someone please help me out. I am trying to build a pivot table that will auto update and within the table do groupings by week. My source data headings are on Row 1 and data covers columns A-H. One of the sticking points I have is that their are some blanks in columns C,R,G,H. Can anyone tell me how to get this to work?
i think you are trying to find data source of the pivot table should be dynamic. very flexible to capture the source data whether new columns added or deleted.

this can achieved by using named range for pivot source.

insert a named range called "PIVOTDATASOURCE"
=OFFSET(INDIRECT("Sheet1!$A$1"),0,0,MAX(COUNTA(IND IRECT("Sheet1!$A:$A")),2),COUNTA(INDIRECT("Sheet1! $1:$1")))

replace sheet1 with required sheet name as per your requirement in the formula.

now instead of selecting the data source manually, give PIVOTDATASOURCE

all the best
__________________
Thanks
Bala
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot tables and dynamic columns excelboy Charts and Charting in Excel 4 August 17th 06 03:23 AM
Datasource for pivot tables Bill Excel Discussion (Misc queries) 3 August 3rd 06 10:15 PM
Pivot Tables -changing datasource for exsting Pivot Table kfschaefer Setting up and Configuration of Excel 0 May 30th 06 06:36 PM
Refreshing Pivot Tables linked to Oracle Datasource [email protected] Excel Discussion (Misc queries) 0 April 25th 06 12:15 AM
Dynamic Pivot tables Phil Excel Discussion (Misc queries) 3 January 31st 05 04:33 PM


All times are GMT +1. The time now is 08:03 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"