Pivot Table Count Issue

I'm using Excel 2003.  I'm a high school guidance secretary.  The 
administrators are constantly asking for data relating to course failures.  
From our school's student management program, I've exported a listing of all 
course grades for the year and used the data to create a pivot table.  

Right now, I'm only using row fields:  StuName, GradeLVL, TermID, StoreCode, 
Course, Grade

I've filtered the data to only show records with a TermID of '1901' & 
'1900'. (Semester 1 Final or Year Long).  I've also filtered the StoreCode to 
only show records with a StoreCode of 'S1'.  I've filtered to Grade data to 
only show records with a grade < 75.  (Course Failures)

My delima is that I need to show ONLY records for students with two or more 
course failures.  Currently, I'm sorting through the records and hiding the 
rows for students with only one course failure.  It seems like there should 
be a way for Excel to calculate this data for me, but I don't know how in the 
Pivot Table.

Can you help?

Thanks!
0
Utf
4/19/2010 7:35:01 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
425 Views

Similar Articles

[PageSpeed] 26

Maybe you could add another column "Courses Failed" to the raw data.

If stuname is in column A and grade is in column F, then a formula like this in
G2 (nice header in G1):

=sumproduct(--(A2:A999=a2),--(F2:F999<75))

Adjust the ranges to match--but you can't use whole columns (except in xl2007+). 

=sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

=======
Then you can use this new field as a page field and hide the records that are
less than 2.

(or modify the formula in G2:
=sumproduct(--(A2:A999=a2),--(F2:F999<75))>1

This will return True if the count is more than one.

========
When you move to xl2007+, you'll be able to use an =countifs() formula that can
check more than one column.



KingdomGirl wrote:
> 
> I'm using Excel 2003.  I'm a high school guidance secretary.  The
> administrators are constantly asking for data relating to course failures.
> From our school's student management program, I've exported a listing of all
> course grades for the year and used the data to create a pivot table.
> 
> Right now, I'm only using row fields:  StuName, GradeLVL, TermID, StoreCode,
> Course, Grade
> 
> I've filtered the data to only show records with a TermID of '1901' &
> '1900'. (Semester 1 Final or Year Long).  I've also filtered the StoreCode to
> only show records with a StoreCode of 'S1'.  I've filtered to Grade data to
> only show records with a grade < 75.  (Course Failures)
> 
> My delima is that I need to show ONLY records for students with two or more
> course failures.  Currently, I'm sorting through the records and hiding the
> rows for students with only one course failure.  It seems like there should
> be a way for Excel to calculate this data for me, but I don't know how in the
> Pivot Table.
> 
> Can you help?
> 
> Thanks!

-- 

Dave Peterson
0
Dave
4/19/2010 9:12:27 PM
Hi, Dave.  Thank you so much for your help.  I consider myself pretty 
proficient in Excel, but the SUMPRODUCT formula is completely new to me.  I'm 
excited about the possibilities.

I'm not sure what I'm doing wrong, but I can't get the formula to return the 
correct number.  As an example: rows A2:A26 contain records for a single 
student.  (We'll call him John.)  There are multiple rows for each student 
because each row represents a course that student has taken.  Column A 
contains the student name.  Column F contains the course grade.  I entered 
the SUMPRODUCT formula into Column G, as you suggested.  The result returned 
was '17', but that is not correct.  Looking at John's grades, he actually has 
failed 7 courses.  (Obviously not our best student!)

I don't know if it makes a difference, but I feel I should mention that my 
data represents each student's entire course history, so the file is quite 
large - over 30,000 rows.

Do you have any idea what I'm doing wrong?

Thanks again for your EXCELLENT help!

Julie



"Dave Peterson" wrote:

> Maybe you could add another column "Courses Failed" to the raw data.
> 
> If stuname is in column A and grade is in column F, then a formula like this in
> G2 (nice header in G1):
> 
> =sumproduct(--(A2:A999=a2),--(F2:F999<75))
> 
> Adjust the ranges to match--but you can't use whole columns (except in xl2007+). 
> 
> =sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
> to 1's and 0's.
> 
> Bob Phillips explains =sumproduct() in much more detail here:
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> 
> And J.E. McGimpsey has some notes at:
> http://mcgimpsey.com/excel/formulae/doubleneg.html
> 
> =======
> Then you can use this new field as a page field and hide the records that are
> less than 2.
> 
> (or modify the formula in G2:
> =sumproduct(--(A2:A999=a2),--(F2:F999<75))>1
> 
> This will return True if the count is more than one.
> 
> ========
> When you move to xl2007+, you'll be able to use an =countifs() formula that can
> check more than one column.
> 
> 
> 
> KingdomGirl wrote:
> > 
> > I'm using Excel 2003.  I'm a high school guidance secretary.  The
> > administrators are constantly asking for data relating to course failures.
> > From our school's student management program, I've exported a listing of all
> > course grades for the year and used the data to create a pivot table.
> > 
> > Right now, I'm only using row fields:  StuName, GradeLVL, TermID, StoreCode,
> > Course, Grade
> > 
> > I've filtered the data to only show records with a TermID of '1901' &
> > '1900'. (Semester 1 Final or Year Long).  I've also filtered the StoreCode to
> > only show records with a StoreCode of 'S1'.  I've filtered to Grade data to
> > only show records with a grade < 75.  (Course Failures)
> > 
> > My delima is that I need to show ONLY records for students with two or more
> > course failures.  Currently, I'm sorting through the records and hiding the
> > rows for students with only one course failure.  It seems like there should
> > be a way for Excel to calculate this data for me, but I don't know how in the
> > Pivot Table.
> > 
> > Can you help?
> > 
> > Thanks!
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
4/22/2010 7:05:01 PM
What formula did you use?  (Just to make sure you didn't change it too much
<vbg>.)

Are there scores where John showed 75, but where is actual score is 74.9 -- and
the cell is formatted to not show decimals (or just to skinny to show them)?



KingdomGirl wrote:
> 
> Hi, Dave.  Thank you so much for your help.  I consider myself pretty
> proficient in Excel, but the SUMPRODUCT formula is completely new to me.  I'm
> excited about the possibilities.
> 
> I'm not sure what I'm doing wrong, but I can't get the formula to return the
> correct number.  As an example: rows A2:A26 contain records for a single
> student.  (We'll call him John.)  There are multiple rows for each student
> because each row represents a course that student has taken.  Column A
> contains the student name.  Column F contains the course grade.  I entered
> the SUMPRODUCT formula into Column G, as you suggested.  The result returned
> was '17', but that is not correct.  Looking at John's grades, he actually has
> failed 7 courses.  (Obviously not our best student!)
> 
> I don't know if it makes a difference, but I feel I should mention that my
> data represents each student's entire course history, so the file is quite
> large - over 30,000 rows.
> 
> Do you have any idea what I'm doing wrong?
> 
> Thanks again for your EXCELLENT help!
> 
> Julie
> 
> "Dave Peterson" wrote:
> 
> > Maybe you could add another column "Courses Failed" to the raw data.
> >
> > If stuname is in column A and grade is in column F, then a formula like this in
> > G2 (nice header in G1):
> >
> > =sumproduct(--(A2:A999=a2),--(F2:F999<75))
> >
> > Adjust the ranges to match--but you can't use whole columns (except in xl2007+).
> >
> > =sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
> > to 1's and 0's.
> >
> > Bob Phillips explains =sumproduct() in much more detail here:
> > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> >
> > And J.E. McGimpsey has some notes at:
> > http://mcgimpsey.com/excel/formulae/doubleneg.html
> >
> > =======
> > Then you can use this new field as a page field and hide the records that are
> > less than 2.
> >
> > (or modify the formula in G2:
> > =sumproduct(--(A2:A999=a2),--(F2:F999<75))>1
> >
> > This will return True if the count is more than one.
> >
> > ========
> > When you move to xl2007+, you'll be able to use an =countifs() formula that can
> > check more than one column.
> >
> >
> >
> > KingdomGirl wrote:
> > >
> > > I'm using Excel 2003.  I'm a high school guidance secretary.  The
> > > administrators are constantly asking for data relating to course failures.
> > > From our school's student management program, I've exported a listing of all
> > > course grades for the year and used the data to create a pivot table.
> > >
> > > Right now, I'm only using row fields:  StuName, GradeLVL, TermID, StoreCode,
> > > Course, Grade
> > >
> > > I've filtered the data to only show records with a TermID of '1901' &
> > > '1900'. (Semester 1 Final or Year Long).  I've also filtered the StoreCode to
> > > only show records with a StoreCode of 'S1'.  I've filtered to Grade data to
> > > only show records with a grade < 75.  (Course Failures)
> > >
> > > My delima is that I need to show ONLY records for students with two or more
> > > course failures.  Currently, I'm sorting through the records and hiding the
> > > rows for students with only one course failure.  It seems like there should
> > > be a way for Excel to calculate this data for me, but I don't know how in the
> > > Pivot Table.
> > >
> > > Can you help?
> > >
> > > Thanks!
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
4/22/2010 7:17:02 PM
ps.

I should have suggested a formula that didn't change the range to inspect:

=sumproduct(--(A2:A999=a2),--(F2:F999<75))
should have been:
=sumproduct(--($A$2:$A$999=a2),--($F$2:$F$999<75))

Then dragging down won't break the formula.

KingdomGirl wrote:
> 
> Hi, Dave.  Thank you so much for your help.  I consider myself pretty
> proficient in Excel, but the SUMPRODUCT formula is completely new to me.  I'm
> excited about the possibilities.
> 
> I'm not sure what I'm doing wrong, but I can't get the formula to return the
> correct number.  As an example: rows A2:A26 contain records for a single
> student.  (We'll call him John.)  There are multiple rows for each student
> because each row represents a course that student has taken.  Column A
> contains the student name.  Column F contains the course grade.  I entered
> the SUMPRODUCT formula into Column G, as you suggested.  The result returned
> was '17', but that is not correct.  Looking at John's grades, he actually has
> failed 7 courses.  (Obviously not our best student!)
> 
> I don't know if it makes a difference, but I feel I should mention that my
> data represents each student's entire course history, so the file is quite
> large - over 30,000 rows.
> 
> Do you have any idea what I'm doing wrong?
> 
> Thanks again for your EXCELLENT help!
> 
> Julie
> 
> "Dave Peterson" wrote:
> 
> > Maybe you could add another column "Courses Failed" to the raw data.
> >
> > If stuname is in column A and grade is in column F, then a formula like this in
> > G2 (nice header in G1):
> >
> > =sumproduct(--(A2:A999=a2),--(F2:F999<75))
> >
> > Adjust the ranges to match--but you can't use whole columns (except in xl2007+).
> >
> > =sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
> > to 1's and 0's.
> >
> > Bob Phillips explains =sumproduct() in much more detail here:
> > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> >
> > And J.E. McGimpsey has some notes at:
> > http://mcgimpsey.com/excel/formulae/doubleneg.html
> >
> > =======
> > Then you can use this new field as a page field and hide the records that are
> > less than 2.
> >
> > (or modify the formula in G2:
> > =sumproduct(--(A2:A999=a2),--(F2:F999<75))>1
> >
> > This will return True if the count is more than one.
> >
> > ========
> > When you move to xl2007+, you'll be able to use an =countifs() formula that can
> > check more than one column.
> >
> >
> >
> > KingdomGirl wrote:
> > >
> > > I'm using Excel 2003.  I'm a high school guidance secretary.  The
> > > administrators are constantly asking for data relating to course failures.
> > > From our school's student management program, I've exported a listing of all
> > > course grades for the year and used the data to create a pivot table.
> > >
> > > Right now, I'm only using row fields:  StuName, GradeLVL, TermID, StoreCode,
> > > Course, Grade
> > >
> > > I've filtered the data to only show records with a TermID of '1901' &
> > > '1900'. (Semester 1 Final or Year Long).  I've also filtered the StoreCode to
> > > only show records with a StoreCode of 'S1'.  I've filtered to Grade data to
> > > only show records with a grade < 75.  (Course Failures)
> > >
> > > My delima is that I need to show ONLY records for students with two or more
> > > course failures.  Currently, I'm sorting through the records and hiding the
> > > rows for students with only one course failure.  It seems like there should
> > > be a way for Excel to calculate this data for me, but I don't know how in the
> > > Pivot Table.
> > >
> > > Can you help?
> > >
> > > Thanks!
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
4/22/2010 7:18:37 PM
Okay, I posted too quickly.  Figured out my problem.  Actually, there were 
two.  The first is that some courses did not have a grade, so the blank was 
calculated as a zero.  That's where the 17 came from.  The second problem is 
because there are multiple rows for every student, the number of failures 
decreased as the formula was copied down.

As a solution, I deleted all rows without grades.  I reconfigured my rows so 
the Student Number is now in column A.  Then, I sorted by Student Number in 
ascending order and Course Failure in descending order.  I inserted another 
worksheet and inserted all student numbers into column A, with a separate row 
for each student.  I entered a VLOOKUP formula into column B to reference the 
student number on the data sheet and return the number of course failures.  

So now I'm left with a true count of course failures for each student, which 
brings up another delima:  In any given grading period, I will need to know 
how many course failures a student had in the previous term.  The count I 
have now is for the number of course failures a student has had in his/her 
entire high school career.  What is the solution?  Perhaps insert colums for 
course failures by term, and use an IF formula?  Something like:

=IF(AND(D2=1901,F2<75),1,0)

Then I could use the SUMPRODUCT formula to count the numer of failures.

What do you think?  Am I making this too complicated?

Thanks for your help!

Julie

"Dave Peterson" wrote:

> Maybe you could add another column "Courses Failed" to the raw data.
> 
> If stuname is in column A and grade is in column F, then a formula like this in
> G2 (nice header in G1):
> 
> =sumproduct(--(A2:A999=a2),--(F2:F999<75))
> 
> Adjust the ranges to match--but you can't use whole columns (except in xl2007+). 
> 
> =sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
> to 1's and 0's.
> 
> Bob Phillips explains =sumproduct() in much more detail here:
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> 
> And J.E. McGimpsey has some notes at:
> http://mcgimpsey.com/excel/formulae/doubleneg.html
> 
> =======
> Then you can use this new field as a page field and hide the records that are
> less than 2.
> 
> (or modify the formula in G2:
> =sumproduct(--(A2:A999=a2),--(F2:F999<75))>1
> 
> This will return True if the count is more than one.
> 
> ========
> When you move to xl2007+, you'll be able to use an =countifs() formula that can
> check more than one column.
> 
> 
> 
> KingdomGirl wrote:
> > 
> > I'm using Excel 2003.  I'm a high school guidance secretary.  The
> > administrators are constantly asking for data relating to course failures.
> > From our school's student management program, I've exported a listing of all
> > course grades for the year and used the data to create a pivot table.
> > 
> > Right now, I'm only using row fields:  StuName, GradeLVL, TermID, StoreCode,
> > Course, Grade
> > 
> > I've filtered the data to only show records with a TermID of '1901' &
> > '1900'. (Semester 1 Final or Year Long).  I've also filtered the StoreCode to
> > only show records with a StoreCode of 'S1'.  I've filtered to Grade data to
> > only show records with a grade < 75.  (Course Failures)
> > 
> > My delima is that I need to show ONLY records for students with two or more
> > course failures.  Currently, I'm sorting through the records and hiding the
> > rows for students with only one course failure.  It seems like there should
> > be a way for Excel to calculate this data for me, but I don't know how in the
> > Pivot Table.
> > 
> > Can you help?
> > 
> > Thanks!
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
4/22/2010 7:45:01 PM
First, I posted a modification to my initial suggestion that fixed that range:

=sumproduct(--($A$2:$A$999=a2),--($F$2:$F$999<75))

You'll want to add one more criteria to this to make sure the value in column F
is a number:

=sumproduct(--($A$2:$A$999=a2),--($F$2:$F$999<75),--(isnumber($f$2:$f$999)))

(You shouldn't have to delete those rows with empty cells in column F.)

=======
As for the previous term, this formula seems ok to me -- maybe watch out for
those empty cells:
=IF(AND(D2=1901,F2<75,isnumber(f2)),1,0)

This is a shorthand version that will give the same results:
=--(and(D2=1901,F2<75,isnumber(f2)))

The =and() will return true/false.  The first minus will change true to -1 (and
false to 0) and the second minus will change the -1 to +1.  

But use the one you're comfortable with.

One thing that I would consider doing is to dedicate a cell to hold that
previous term.

Then you'll just have to change it one location (instead of all formulas):

=IF(AND(D2=x99,F2<75,isnumber(f2)),1,0)
or
=--(and(D2=x99,F2<75,isnumber(f2)))

I used X99, but you'll want it closer/easier to see.

And remember that if you have a Text value of '1901, then it's not equal to the
number 1901.  




KingdomGirl wrote:
> 
> Okay, I posted too quickly.  Figured out my problem.  Actually, there were
> two.  The first is that some courses did not have a grade, so the blank was
> calculated as a zero.  That's where the 17 came from.  The second problem is
> because there are multiple rows for every student, the number of failures
> decreased as the formula was copied down.
> 
> As a solution, I deleted all rows without grades.  I reconfigured my rows so
> the Student Number is now in column A.  Then, I sorted by Student Number in
> ascending order and Course Failure in descending order.  I inserted another
> worksheet and inserted all student numbers into column A, with a separate row
> for each student.  I entered a VLOOKUP formula into column B to reference the
> student number on the data sheet and return the number of course failures.
> 
> So now I'm left with a true count of course failures for each student, which
> brings up another delima:  In any given grading period, I will need to know
> how many course failures a student had in the previous term.  The count I
> have now is for the number of course failures a student has had in his/her
> entire high school career.  What is the solution?  Perhaps insert colums for
> course failures by term, and use an IF formula?  Something like:
> 
> =IF(AND(D2=1901,F2<75),1,0)
> 
> Then I could use the SUMPRODUCT formula to count the numer of failures.
> 
> What do you think?  Am I making this too complicated?
> 
> Thanks for your help!
> 
> Julie
> 
> "Dave Peterson" wrote:
> 
> > Maybe you could add another column "Courses Failed" to the raw data.
> >
> > If stuname is in column A and grade is in column F, then a formula like this in
> > G2 (nice header in G1):
> >
> > =sumproduct(--(A2:A999=a2),--(F2:F999<75))
> >
> > Adjust the ranges to match--but you can't use whole columns (except in xl2007+).
> >
> > =sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
> > to 1's and 0's.
> >
> > Bob Phillips explains =sumproduct() in much more detail here:
> > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> >
> > And J.E. McGimpsey has some notes at:
> > http://mcgimpsey.com/excel/formulae/doubleneg.html
> >
> > =======
> > Then you can use this new field as a page field and hide the records that are
> > less than 2.
> >
> > (or modify the formula in G2:
> > =sumproduct(--(A2:A999=a2),--(F2:F999<75))>1
> >
> > This will return True if the count is more than one.
> >
> > ========
> > When you move to xl2007+, you'll be able to use an =countifs() formula that can
> > check more than one column.
> >
> >
> >
> > KingdomGirl wrote:
> > >
> > > I'm using Excel 2003.  I'm a high school guidance secretary.  The
> > > administrators are constantly asking for data relating to course failures.
> > > From our school's student management program, I've exported a listing of all
> > > course grades for the year and used the data to create a pivot table.
> > >
> > > Right now, I'm only using row fields:  StuName, GradeLVL, TermID, StoreCode,
> > > Course, Grade
> > >
> > > I've filtered the data to only show records with a TermID of '1901' &
> > > '1900'. (Semester 1 Final or Year Long).  I've also filtered the StoreCode to
> > > only show records with a StoreCode of 'S1'.  I've filtered to Grade data to
> > > only show records with a grade < 75.  (Course Failures)
> > >
> > > My delima is that I need to show ONLY records for students with two or more
> > > course failures.  Currently, I'm sorting through the records and hiding the
> > > rows for students with only one course failure.  It seems like there should
> > > be a way for Excel to calculate this data for me, but I don't know how in the
> > > Pivot Table.
> > >
> > > Can you help?
> > >
> > > Thanks!
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
4/22/2010 9:53:15 PM
Reply:

Similar Artilces:

Pivot Tables #4
Hello you experts out there. I nned some Pivot table advice ( a website address for basics would be great), for instance how to excplain pivots and how to create them. I use lots of products with lots of sizes from various suppliers to name but a few columns. Is there a limit as to the amount of columns of data? Thank you very much. H Hi check out Debra Dalgleish's site - http://www.contextures.com/tiptech.html - she has a link to Jon Peltier's site which explains PT basics and then lots more interesting info on them. -- Cheers JulieD check out www.hcts.net.au/tipsandtric...

Pivot Table Counting
I am having trouble trying to calculate fields through a pivot table. Basically, I have a data set that looks like this: Record Number State Value 1 CA 0 2 CA 12 3 CA 15 4 NJ 0 5 NJ 10 I want to set up a pivot table that will show 2 columns, one with th total number of records and one with the total number of records wit non-zero values. Both of the...

Pivot Tables
Hi, I was windering if some one could provide me with a link to a tutorial for what I am trying to do. I have an aggregate table. This table has several groups and several columns. The intersection of a group and column is an aggreate value such as Sum function of a column value in that group. Normally a Pivot table shows 1 aggregate value at the intersection of a group and a column. What I want is: To show more than one value at the intersection besides the aggregate value. In other words, if a simple pivot table cell is: Aggr(x,y) where aggr is an aggregate function such a...

Pivot Chart Formatting #5
How can I format a Pivot Chart and keep the customized format when refreshed? This is a known issue by Microsoft. Short answer, you can't do it. =( One possible work around is to record a macro of you setting the formatting of the chart, and then set it up so that the macro runs whenever the chart calculates. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Germeny Santos" wrote: > How can I format a Pivot Chart and keep the customized format when refreshed? Hi, This is dependent on what custom formatting you are refering to. T...

Pivot table question: Sorting the subgroup in a pivot table (when using the top 10 option for the subgroup)
Hello: I have created a pivot table which has a one subgroup in the second column from the left. It uses the Top 10 option, so there are 10 subgroups So my data looks something like this: Group 1 50 Subgroup 1a 10 Subgroup 1b 40 Eight more subgroups Group 2 30 Subgroup 2a 20 Subgroup 2b 10 Eight more subgroups What I would like to do is sort by THe totals for Group 1, but then also sort by the subgroups, based on the values for each subgroup (similar to the "Sort by, ...

Count blanks by date
I have a table that has meter# , date, and usage in colums A, B and C. I use a formula =sumif($B$11:$B1000,"="[date],$C$11:$C1000) to total up the amount of usage for each day. But what I want to do is count the number of blanks in column C for each date. So "if $B$11:$B1000,"="[date] then countblank($C$11:$C1000)" is kind of what I'm shooting for but I can't wrap my brain around how to do it. I would like to report that on 4/1 we had 1,912 usage and 4 meters did not report (were blank). Thanks! Try =SUMPRODUCT(--($B$11:$B1000=[date]),-...

Conditional formatting and then counting specifics within it.
I have a work book that can have up to 100 sheets within it. An example of a sheet is:- Col B Col F Col N Col AM Col AN Col AO Col AP Test 1 12/03/03 1 01/01/03 31/03/03 01/01/02 31/03/02 Test 2 01/01/03 5 01/04/03 31/08/03 01/04/02 31/08/02 Test 3 01/09/03 2 01/09/03 31/12/03 01/09/02 31/12/02 Test 4 12/12/03 2 Test 5 14/04/03 3 Test 6 12/03/03 1 Column B hold a name. Column F holds a date of birth. Column N hold a number in the range 1 to 5. Columns AM to AP are start and end dates for current year and previous year for specific periods in the format dd/mm/yy. I have used 3 Conditiona...

Counting if settings are "Final", or "Draft" #2
Sorry, that looks really bad. I hope this is better, ColA Col B Col C A1 S:/Tax1Volume/Forms_03/01_Sep_03 A2 Draft Doc A A3 S:/Tax1Volume/Forms_03/02_Sep_10 A4 Draft Doc B A5 S:/Tax1Volume/Forms_03/03_Sep_17 A6 Draft Doc C A7 Draft Doc D A8 S:/Tax1Volume/Forms_03/04_Sep_23 A9 Draft Doc E A10 Draft Doc F A11 Final Doc C A12 Draft Doc G A13 S:/Tax1Volume/Forms_03/05_Sep_27 A14 Draft Doc H A15 Final Doc B A16 S:/Tax1Volume/Forms_03/05_Sep_28 A17 Final Doc A ...

join multiple pivot tables?
How can I link up multiple pivottables all based on the same data where all i would have to do is change the page option (drop down menu) from the first pivot and the rest of the pivots would automatically adjust to match those? There's a sample file here in which you can change the page field in one pivot table, and all the related pivot tables change: http://www.contextures.com/excelfiles.html Under Pivot Tables, look for 'PT0021 - Change All Page Fields' or 'PT0008 - Change Multiple Page Fields' clueless_in_mempjhis wrote: > How can I link up mu...

no response from Microsoft customer support
I am concerned because I have not received a response from my inquiry. I had even reported it through the microsoft product support section, for which a number was displayed as confirmation, but no email response has been received. Thanks so much... Until this incident I have been VERY PLEASED with Microsoft support. Now I am suspecting a virus or something intercepting my requests because I can't imagine that Microsoft would not respond to my concerns... Unless I get some kind of response the only thing I can do is speculate 1. That Microsoft policies have changed re...

Help with pivot table
I'm after some help with a pivot table I have created a work book to log certain defects, and would like to use a pivot table to show defects per unit on an improvement tracker, is it possible to have the pivot table to shift cells to the right when it is updated and required more columns so it doesn't overwrite data on the sheet it would be like this the componet fault unit numbers & Total defects are the pivot table & the action would be the start of the manual data Compontent Fault Unit Numbers Total Defects Action 1 2 3 4 5 Light Inop 1 1 ...

Can I permanently delete/hide data within Pivot Tables?
Microsoft Excel 2002 I have 155 clients which I need to run Pivot Tables for! All with the same pivot table layout. Is it possible to run the Pivot table with the 155 clients as a Template basically, then somehow delete or permanently hide the data leaving only one client, so that I can save multiple copies for each of the individual clients? Or can I set up a pivot table where I can automatically feed in parameters? Client details are stored on an Oracle database! Hi Peter, Right-click on a Page Field and select Field Settings from the context menu, then click the Advanced button. Yo...

Counting Non Blank Cells
I could really use some help with this headache. I'm using Excel 2003. I'm doing a Vlookup comparing text data in Column A of Worksheet 1 to text data in Column C of Worksheet 2. If there's a match I'm having the result placed in Column B of Worksheet 1. When there's no match I get a blank which is fine. I'm comparing about 10,000 records in Worksheet 1 to about 8,000 records in Worksheet 2. I need a way to get a count of only the matched records in Column B. I've tried using the The CountA function for Column B, but I get the exact same counts in Column B ...

Using formulas for pivot table
I know you have the count field but is there away to input a formula? For example customer ordered 25 cases and each case weighs 4 lbs and the end result would need to be total pounds ordered. Thanks! If you do the calculation in the pivot table you may not get the result that you expect. If possible, add a field to the source data, and calculate the order total there. Then, add the OrderTotal field to the pivot table's data area. tskb wrote: > I know you have the count field but is there away to input a formula? > For example customer ordered 25 cases and each case weighs 4 lbs...

Hyperlink in Pivot Table
Is it possible to put hyperlinks into a pivot table to go to the detail in other worksheets? I have a top 10 list of components with issues and would like the user to be able to click on the component to go to the list of issues. I thought if I put the hyperlink in the cell where the pivot table is getting it's info, it would come through, but that didn't work. Any suggestions? Thanks Hi Just double clicking on the cell with the data of interest, will open another sheet showing all of the entries making up that value. Delete the newly created sheet after you have finished w...

chart options show data table
Currently using 2 workbooks to produce 1 chart - the source data in workbook 1 and the chart in workbook 2. We also are using the "show data table" option to most easily view all values in the chart. We would like this data to keep it's currency format to best reflect the values in the chart; however, the data table loses this formatting when we close workbook 1. Is there any way to hang onto this formatting after the workbook is closed? Data tables in charts are not particularly flexible or capable. You combine the workbooks. Alternatively, put a table in the chart's...

Flowcharting Table relations
Hey guys and girls. New one for you. I am doing an IT audit of Great Plains and need to flowchart how the data files are related to each other. In other words, how does one file feed into another file, and what field or fields in a particular table is used to relate/link to another table. Does anyone where I can find such information? Thanks Check out the Great Plains SDK available on the installation CDs. There are several diagrams that describe table relationships. Also, there are module documents that contain posting flowcharts. Regards, Kevin Rood Corporate Software Consultants...

Pivot Tables #40
Does anyone know if there is a way to quickly clear a field list? I can't find a keyboard shortcut or anything else in any of the help/support avenues I've tried. Thanks in advance for the help. -- ERR229 Are you trying to clear all the check boxes in the field's dropdown list? In Excel 2002 and later versions, there's a Show All checkbox. In Excel 2000, and earlier versions, you can use programming to hide or show the items -- there's sample code here: http://www.contextures.com/xlPivot03.html ERR229 wrote: > Does anyone know if there is a way to quickl...

I need to convert a word table to excel
When I try doing this by copying the table it appears in excelwith one or two empty lines in between each of my lines that I needed converting. Why is this? usually means there are superfluous paragraph marks in the Word table. "Aaron" <Aaron@discussions.microsoft.com> wrote in message news:012E3413-1F64-4018-BC14-A2D6AB68B668@microsoft.com... > When I try doing this by copying the table it appears in excelwith one or > two > empty lines in between each of my lines that I needed converting. Why is > this? ...

line column Pivot Charts
Access 2003 does not support combined Line/Column Charts, but Excel does. Has the capability been added to Access 2007? Access does support a series displayed as a bars and another series as line. -- Duane Hookom MS Access MVP Help me support UCP http://www.access.hookom.net/UCP/Default.htm "John" <John@discussions.microsoft.com> wrote in message news:A0DD25F6-D99A-44C3-812B-416776BED8AF@microsoft.com... > Access 2003 does not support combined Line/Column Charts, but Excel does. > Has > the capability been added to Access 2007? ...

Stock Count Entry #6
A flaw in the reconciliation of the stock count entry is that if there is a variance between system and actual counts, it is almost impossible to reconcile by virtue of the fact that the unposted transactions screen omits to tell the quantities relating to each transaction. You manually have to go into each transaction to determine the quantity, and then keep a running total manually. Surely anyone doing a reconciliation would need to at least know the total of the unposted transaction quantities, otherwise, how is the variance going to be entered correctly? ...

deactivate the get pivot formula when I link a cell to a pivot
Currently I using excel 2003, but every time when I link the cell to a pivot table it will convert to a "Get pivot" formula. Is there any way I could deactivate it? See Debra Dalgleish's website for instructions: http://www.contextures.com/xlPivot06.html Does that help? *********** Regards, Ron XL2002, WinXP-Pro "hwtradezheng" wrote: > Currently I using excel 2003, but every time when I link the cell to a pivot > table it will convert to a "Get pivot" formula. Is there any way I could > deactivate it? ...

pivot table error (25000 rows!!)
I am trying to run a pivot table on a file containing 25000 rows. I have 5 duplicate (identical structure) files. The pivot works on each but one. On this file I get an error saying it has too many columns or rows in the pivot table. Drag at least one column or row off the table... Does anybody know what the reason for this message is, are their limits to rows or columns? The identical table works on the other files, therefore this message is so strange. Who has an idea? -- Craig_Richards ------------------------------------------------------------------------ Craig_Richards's Profil...

Pivot table move up command
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I need to move up values in a pivot table to custom order them. When I right-click I see the &quot;Move&quot; menu, but the options to move up, move down, move to beginning and move to end are greyed out. Did you find an answer? Katy MelKC wrote: Pivot table move up command 04-Feb-10 Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I need to move up values in a pivot table to custom order them. When I right-click I see the &quot;Move&quot; menu, but the options to move up, move d...

STARTTLS issue just started occurring on EX2000
I have remote users connecting using imap/SMTP for mail. We had to reboot the server over the weekend and now we are seeing the following issue Task 'webmail.madden.com - Sending' reported error (0x800CCC78) : 'Unable to send the message. Please verify the e-mail address in your account properties. The server responded: 505 5.7.3 Must issue a STARTTLS command first' Imap seems fine but this SMTP connector seems hosed. I have removed the certificate since this seems to be a SSL issue and have told Outlook 2003 to not authenticate. None of these has helped. This server...