Hi
Glad you found the links useful. All the thanks though is really due to
them for providing us all with such valuable resource.
I usually get around the problem of blank dates by making the blanks a
date way into the future, rather than blank.
Then, you can use Grouping by Date, but set the range of dates to
Exclude the far distant dates.
--
Regards
Roger Govier
"RS" wrote in message
...
Dear Roger,
Thanks for your response. You're right in that my client has some
blank cells in the closing date column hence I can't group the cells
by
month, although the PivotTable does generate a row at the bottom
called
(blanks).
I also just quickly checked the links which you provided and found
them
to be incredibly useful. Coincidentally (& for the first time), I had
found
a solution to a autofiltering and password protection problem just a
couple
of days ago from Debra Dalgleish's Contextures website. She provided
a
solution that many other posts in different forums (Google groups &
even
Microsoft's official help topic on this issue!) didn't solve.
Also, the link to Mike Alexander's DataPig Excel Training site is
absolutely incredible! I checked out his info on PivotTable grouping
and
when I clicked on his link, there was a Flash video tutorial
explaining
exactly how to do that!! As much as I don't mind reading instructions
on how
to do something, I feel that watching a video of what needs to be done
is
much more effective and SO MUCH QUICKER than reading those same
instructions.
Thank you so much for your assistance (ESPECIALLY the 2 links that you
provided)!!!!!!
"Roger Govier" wrote:
Hi
If you are having problems with Grouping your Dates by Month, it
either
means that some cells within the range are not true Excel dates, or
are
blank cells. The PT will not do the grouping unless every cell
contains
a valid date.
If you have made the overall range in your original selection of
data,
longer than that currently used to allow for more data being added,
that
could be the problem. Limit the range to just the used area or create
a
Dynamic range for your data (for help on this see below)
The Page area, is one of 3 different areas to which you can allocate
fields (columns) from your raw data.
For more help on setting up and using Pivot tables go to Debra
Dalgleish's site
http://www.contextures.com/tiptech.html
and scroll to the section on Pivot Tables
and for Dynamic Range naming
http://www.contextures.com/xlNames01.html#Dynamic
or Mike Alexander's site
http://www.datapigtechnologies.com/ExcelMain.htm
http://www.datapigtechnologies.com/f...es/pivot1.html
Do persist with getting to grips with Pivot Tables; you will find
them
invaluable.
--
Regards
Roger Govier
"Ron Coderre" wrote in message
...
Would you consider letting a Pivot Table do the heavy lifting?
Try this:
<Data<Pivot Table
Use: Excel
Select your data
Click the [Layout] button
ROW: Drag the ClosingDate field here
COLUMN: Drag the ProgramType field here
DATA: Drag the ProgramType field here
If it doesn't list as Count of ProgramType...dbl-click it and set
it
to Count
(also...you can rename "Count of ProgramType" to something more
appropriate)
Click [OK]
Select where you want the Pivot Table...and Click the [Finish]
button
(Note: I'm having you temporarily put the ClosingDates on the left
in
case
there are more than 256...Excel's column limit.)
That will list ProgramTypes across the top
ClosingDates down the left
and the count of ProgramTypes.
(not quite there yet...but continue reading)
Right-click on the ClosingDate field
Select: Group and Show Detail
Group by: Months
Click [OK]
Now the left column displays Jan, Feb, Mar...etc...instead of
dates.
After they're grouped, you can drag the ClosingDate field to the
top
and drag the ProgramType to the left
To refresh the Pivot Table, just right click it and select Refresh
Data
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
"RS" wrote:
Hi everyone. I've spent quite a few hours looking all over the
internet and
within this community for the answer to my question. While there
are
various
solutions out there [using SUMPRODUCT for example (which I've
never
used)], I
can't seem to find one that specifically addresses my issue (I'm
sure
it must
be out there but I just can't find it).
Here's the situation my client wants: "...is it possible to track
the
number of program types on a monthly basis?"
In the spreadsheet, there's a column with closing dates (data
starts
in J49
on down; format for dates is m/d/y, example: 7/31/06) and another
with
program types (starting in K49 on down; example: Home). The
programs
types
come from a list of 10 choices located in cells AC14:AC23.
I'm trying to create a separate table on a different worksheet
with
months
as the column headings and the 10 program types as the row
headings.
Here is
part of the new table:
Jul Aug Sep Oct Nov Dec
Home - - - - - -
Kin - - - - - -
FC - - - - - -
IFC - - - - - -
GH - - - - - -
IL - - - - - -
Res - - - - - -
Hosp - - - - - -
STARR - - - - - -
Other - - - - - -
I tried creating a formula to do this but I'm having some
problems.
Here is
what I tried for tracking "Home" in the month of July:
IF(MONTH(Worksheet!$J$49:Worksheet!$J$969,7),COUNT IF(Worksheet!$K$49:Worksheet!$K$969,Worksheet!$AC$ 14))
Even though there are currently only 20 or so rows filled in
I
used
J969 to make sure the formula went far enough down. I know that
Excel
automatically extends formulas but I didn't know if it would also
do
it for
calculations already in the spreadsheet [ex: would sum(M49:M69) or
in
this
case (MONTH(J49:J69))be automatically extended to include
additional
rows of
data as they were added?].
I know that I would need to change the absolute reference
from
$AC$14
to $AC14 when copying the forumlas down the table to include the
other
programs.
I figure that rather than wasting any more hours (already
have
spent
many hours) searching for a solution all over the web, I would
post
my
question to all the experts in this community. I'm sure someone
with
much
greater expertise than I have should be able to solve my problem
fairly
easily. Thank you once again and sorry if this solution has been
answered
before (couldn't find it).