Pivot Table With Dynamic Range Source

[S Jackson]

My datasource table for the pivot table has three columns. The worksheet is
named ANNUAL! There are 3 columns: I used data validation to create a
drop-down list in the first column, the second column is for a date, and the
third column is a number.

I tried to create a pivot table based on a dynamic range. I defined a named
range as follows:
"ANNUAL" refers to:
=OFFSET(ANNUAL!$A$2,0,0,COUNTA(ANNUAL!$A:$A),3)

I then used Annual for my range source for the pivot table. However, the
problem is that I still cannot group the date column in my pivot table! I
get the message that I cannot group that selection. Is there a way to work
around this?

S. Jackson

 

[Ed Ferrero]

Hi S Jackson,

If the Date column contains any non-date values (including blank entries),
you cannot group that field.

One solution if you do have blank dates is to sort the data by date,
then define the named range by counting on the date column, like;
=OFFSET(ANNUAL!$A$2,0,0,COUNTA(ANNUAL!$B:$B),3)

Ed Ferrero
http://edferrero.m6.net/

 

[S Jackson]

Thanks! That worked great!

Hi S Jackson,

If the Date column contains any non-date values (including blank entries),
you cannot group that field.

One solution if you do have blank dates is to sort the data by date,
then define the named range by counting on the date column, like;
=OFFSET(ANNUAL!$A$2,0,0,COUNTA(ANNUAL!$B:$B),3)

Ed Ferrero
http://edferrero.m6.net/

 

[S Jackson]

Okay, 2nd question:

I want to place another pivot table in the same workbook, but on a different
worksheet with a different dynamic data source. This source table has 5
columns:
- Select employee
- Date
- Select Sick Leave Type
- Awarded
- Used

I named the range as follows: SICKLV

=OFFSET(SICK LV!$A$9, 0,0,COUNTA(SICK LV!$B:$B),5)

However, when I attempt to insert the pivot table using the dynamically
named range (SICKLV), I get an error that says "reference is not valid."

Help?
S. Jackson

 

[S Jackson]

I figured out the problem. I have a space in the name of the worksheet.
Once I renamed the worksheet and took out the space and then changed the
name range, it worked fine.

S. Jackson