problem with pivot table grouping

[Jeff W]

Hello,
I'm trying to create a pivot table of invoice date verses
$amount.

If I select the exact cells as the source range when
creating the pivot table, I'm able to do the grouping
properly.

If I hilite entire columns as the range when creating the
pivot table, the grouping will not work properly. If I
right click on the "Invoice Date" field and choose "Group
and Outline -> Group", I get an Excel error box
stating "Cannot group that selectoin".

I'd like create the pivot table by selecting columns for
the range so that range for the pivot table won't have to
be adjusted in the future should I add new rows of info.

Help???
Thanks,
Jeff

[Tom Ogilvy]

If every cell in the pivot table range for that column does not contain a
date, then you can't group.

Use a defined name which uses a formula to dynamically set up the data range

Insert =>Name=>Define
name: Data1
refersto: =Offset(Sheet1!$A$1,0,0,countA(Sheet1!$A:$A),10)

this defines a column A:J down to the last filled row as the data area
(assumes no blanks in column A).

To check the definition for Data1, go to the name box and type Data1, then
hit enter. If set up properly, you data should be highlighted.

Use DATA1 as the source for your pivot table.

You will still need to refresh your table.

Regards,
Tom Ogilvy

"Jeff W" <jeffw...@home.com> wrote in message
news:045301c17752$fa7882f0$3aef2ecf@TKMSFTNGXA09...

[Jeff W]

Thanks Tom... that explains the problem. My data range
does include blanks, though. There are a lot of company
names listed with their potential $$. When I make a sale,
I fill in the date and amount and change another column,
status, to "in". I made a little sort macro that ends up
putting all "in" lines at the bottom of the spreadsheet.

So the range I want to pivot is blocked together at the
bottom of the spreadsheet with no blanks. There are,
however, blanks before this block all the way up to the
column titles (date, $$Total).

Any ideas?
Jeff

>.
>

[Jeff W]

Thanks Tom... that explains the problem. My data range
does include blanks, though. There are a lot of company
names listed with their potential $$. When I make a sale,
I fill in the date and amount and change another column,
status, to "in". I made a little sort macro that ends up
putting all "in" lines at the bottom of the spreadsheet.

So the range I want to pivot is blocked together at the
bottom of the spreadsheet with no blanks. There are,
however, blanks before this block all the way up to the
column titles (date, $$Total).

Any ideas?
Jeff

>-----Original Message-----

>.
>

[Ian Brown FCA]

Jeff,

Replace all your blanks in the date column with the number 0 (formatted as a
date this will show 00/01/1900 or equivalent).

HTH
Regards,

Ian

"Jeff W" <jeffw...@home.com> wrote in message

news:048101c1775c$91688fd0$3bef2ecf@TKMSFTNGXA10...

[Tom Ogilvy]

As long as there isn't anything above the blank cells, this should work:

=OFFSET(Sheet1!$A$1,MIN(IF(Sheet1!$A$1:$A$100<>0,ROW(Sheet1!$A$1:$A$100)))-1
,0,COUNTA(Sheet1!$A$1:$A$100),10)

Use it in your defined name. (Data1)

Change the 100 in each case to the last row you think you will use (in the
future), but be conservative as the more rows you look at the longer it
takes to calculate.

Of course a pivot table requires that the headers be included, so when you
would sort, you would need to sort so the header row appears just above the
non-empty cells.

Regards,
Tom Ogilvy

"Jeff W" <jeffw...@home.com> wrote in message

news:048301c1775c$8be594e0$36ef2ecf@tkmsftngxa12...

[Jeff W]

Tom,
I actually changed it so that the "in" rows get sorted to
the top of the of the spreadsheet (just under the header
row). That makes it a little easier and I was able to use
your first idea:
refersto: =Offset(Sheet1!$A$1,0,0,countA(Sheet1!$A:$A),10)

Now if I add more "in" rows, they will show up in the
pivot table!

To cap it off, I need to also select added info in another
column... Let's say the range returned with the idea above
is a rectangle A1-C10 (based on the last filled cell in
column C-- C10). How do I also include the corresponding
data in column F, F1-F10?

Thanks,
Jeff W

>.
>

[Tom Ogilvy]

I don't believe you can use a discontiguous range as a source for a pivot
table. You would need to make the range from A1:F10 (if we are still
talking about using this as the source for a pivot table - then just don't
use the other columns in your table). If you just want the number of rows
in column F to be based on the filled entries in A, then just define a name
like

Name: DataF
refersto: =Offset(Sheet1!$A$1,0,5,countA(Sheet1!$A:$A),1)

So the range is offset 5 columns to the right (column %) and has the same
row count as A and is one column wide.

Regards,
Tom Ogilvy

"Jeff W" <jeffw...@home.com> wrote in message

news:0b5101c1781f$87c5efe0$36ef2ecf@tkmsftngxa12...

[Jeff W]

Hey, you're pretty smart! Thanks Tom!

>.
>