Quote:
Originally Posted by mztexas
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