Hi,
I have the data set in Excel 2003, of which the following is a
representative:

UAB Title Code Applicants
Zig X 001300 31
Y 001340 22
Z 001350 11
Zag A 002720 23
B 002730 24


I want to add subtotals to count the applicants for each UAB, Zig and
Zag. The UAB column has blank cells, but X,Y and Z are all Zig, and
A,B are Zag. So, what I want is this:

UAB Title Code Applicants
Zig X 001300 31
Y 001340 22
Z 001350 11
Zig Total 64
Zag A 002720 23
B 002730 24
Zag Total 47
Grand Total 111

My machines runs Excel 2003 SP2. When I do Data..Subtotals...Ok
(accepting all the default options), this works fine. On a colleague's
machine, who is running Excel 2003 with NO service packs, it doesn't
work. I get the following results:
UAB Title Code Applicants
Zig X 001300 31
Zig Total 31 <-this should be two rows further down
Y 001340 22
Z 001350 11
Zag A 002720 23
Zag Total 23 <-this should be one row further down
B 002730 24
Grand Total 111


As far as I can tell, this is not the same as the nested subtotals
problem (http://support.microsoft.com/kb/831824). I would like to test
the registry fix suggested by MS anyway, but unfortunately I cannot
edit my registry due to access problems. There doesn't seem to be
anything about this problem on the MS site or in the newsgroups.

Can anyone shed any light on this, or suggest a workaround?
(NB - "Install the most up to date service pack" is an obvious
workaround. Unfortunately, I work in a place with atrociously poor IT
infrastructure and support, so, ridiculously, this is not an option)

Thanks
Andy

Highlight all the used rows in the UAB column, then press F5 (GoTo)
and click on Special then click on Blanks. This will highlight all the
blanks in the column, with an active cell on the first blank. Then
begin to enter a formula by typing = and click on the cell above the
active cell and do CTRL-Enter. This will copy all the Zigs and Zags
down the UAB column (fix the values if you need to sort the data), so
that your Data | Subtotals will now work.

You could always hide the Zigs and Zags by conditional formatting if
you want the sheet to look the same as before.

Hope this helps.

Pete

On Oct 24, 9:52 am, AndyCotgreave wrote:
Hi,
I have the data set in Excel 2003, of which the following is a
representative:

UAB Title Code Applicants
Zig X 001300 31
Y 001340 22
Z 001350 11
Zag A 002720 23
B 002730 24

I want to add subtotals to count the applicants for each UAB, Zig and
Zag. The UAB column has blank cells, but X,Y and Z are all Zig, and
A,B are Zag. So, what I want is this:

UAB Title Code Applicants
Zig X 001300 31
Y 001340 22
Z 001350 11
Zig Total 64
Zag A 002720 23
B 002730 24
Zag Total 47
Grand Total 111

My machines runs Excel 2003 SP2. When I do Data..Subtotals...Ok
(accepting all the default options), this works fine. On a colleague's
machine, who is running Excel 2003 with NO service packs, it doesn't
work. I get the following results:
UAB Title Code Applicants
Zig X 001300 31
Zig Total 31 <-this should be two rows further down
Y 001340 22
Z 001350 11
Zag A 002720 23
Zag Total 23 <-this should be one row further down
B 002730 24
Grand Total 111

As far as I can tell, this is not the same as the nested subtotals
problem (http://support.microsoft.com/kb/831824). I would like to test
the registry fix suggested by MS anyway, but unfortunately I cannot
edit my registry due to access problems. There doesn't seem to be
anything about this problem on the MS site or in the newsgroups.

Can anyone shed any light on this, or suggest a workaround?
(NB - "Install the most up to date service pack" is an obvious
workaround. Unfortunately, I work in a place with atrociously poor IT
infrastructure and support, so, ridiculously, this is not an option)

Thanks
Andy

Wow! That helps a treat. I've never used Goto and Special before -
you've just opened up a whole load of new things for me to explore.

Ctrl+Enter - that's also new. Is that essentially entering the same
formula (with relative refererences) into each cell in a selection.
That's really powerful: wish I'd known about that for that last five
years....

On Oct 24, 10:01 am, Pete_UK wrote:
Highlight all the used rows in the UAB column, then press F5 (GoTo)
and click on Special then click on Blanks. This will highlight all the
blanks in the column, with an active cell on the first blank. Then
begin to enter a formula by typing = and click on the cell above the
active cell and do CTRL-Enter. This will copy all the Zigs and Zags
down the UAB column (fix the values if you need to sort the data), so
that your Data | Subtotals will now work.

You could always hide the Zigs and Zags by conditional formatting if
you want the sheet to look the same as before.

Hope this helps.

Glad it was of use to you, Andy - thanks for feeding back.

Pete

On Oct 24, 10:14 am, AndyCotgreave wrote:
Wow! That helps a treat. I've never used Goto and Special before -
you've just opened up a whole load of new things for me to explore.

Ctrl+Enter - that's also new. Is that essentially entering the same
formula (with relative refererences) into each cell in a selection.
That's really powerful: wish I'd known about that for that last five
years....

On Oct 24, 10:01 am, Pete_UK wrote:



Highlight all the used rows in the UAB column, then press F5 (GoTo)
and click on Special then click on Blanks. This will highlight all the
blanks in the column, with an active cell on the first blank. Then
begin to enter a formula by typing = and click on the cell above the
active cell and do CTRL-Enter. This will copy all the Zigs and Zags
down the UAB column (fix the values if you need to sort the data), so
that your Data | Subtotals will now work.

You could always hide the Zigs and Zags by conditional formatting if
you want the sheet to look the same as before.

Hope this helps.- Hide quoted text -

- Show quoted text -