Pivot Table With Dynamic Range Source

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

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/

> 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
>
>

Thanks! That worked great!
"Ed Ferrero" <[email protected]> wrote in message
news:[email protected]...
> 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/
>
> > 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
> >
> >
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> Thanks! That worked great!
> "Ed Ferrero" <[email protected]> wrote in message
> news:[email protected]...
> > 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/
> >
> > > 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
> > >
> > >
> >
> >
>
>

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

"S Jackson" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks! That worked great!
> > "Ed Ferrero" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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/
> > >
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>