Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Table filtering broken by Subtotal function in Excel 2010

I recently added a running total column to a Table. To my surprise, the filtering stopped working. In a column with numerical values, the rows with the selected value remained visible but were followed by other rows containing random values. Clearing the filter generally left a row or two hidden near the end of the table.

However, when I convert the table to a range, filtering starts working again

Simple test code shows this problem;

Data block as a Table
Table1
C D E
8 COL1 COL2 COL3
9 1 4 4
10 2 4 8
11 2 50 58
12 34 49 107
13 34 50 157
14 50 1 158
15 2 160

NOTES
1
2 Table1 defined as C8:E15
3 COL3 maintains a running total of COL2 i.e
E9: =SUBTOTAL(9,$D$8:D9)
The formula in COL3 breaks filtering on the table

Table3
C D
24 COL1 COL2
25 1 4
26 2 4
27 2 50
28 34 49
29 34 50
30 50 1
31 2

NOTES
1
2 Table3 defined as C24:D31
3 Running total column removed from table
Filtering now works on the table


Table converted to a range
C D E
41 COL1 COL2 COL3
42 1 4 4
43 2 4 8
44 2 50 58
45 34 49 107
46 34 50 157
47 50 1 158
48 2 160

NOTES

1 C41:E48 is a copy of Table1 converted to a range
2 COL3 maintains a running total of COL2 i.e
E42: =SUBTOTAL(9,$D$41:D42)
3 Filtering now works on the data block

I have checked online and have seen no reference to this problem. I am surprised since a running total is a common display feature. I am running Excel 2010 on Windows 7 (64 bit)


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default Table filtering broken by Subtotal function in Excel 2010


Seems this problem was reported on 11-Jan-2009 on this news group.
Search for heading ;
rogue rows appear in autofiltered sort

In my opinion, this should be considered an Excel bug. Surprised that it jhas not been fixed
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default Table filtering broken by Subtotal function in Excel 2010

On Sunday, March 6, 2016 at 1:24:44 AM UTC-5, Desmond Walsh wrote:
I recently added a running total column to a Table. To my surprise, the filtering stopped working. In a column with numerical values, the rows with the selected value remained visible but were followed by other rows containing random values. Clearing the filter generally left a row or two hidden near the end of the table.

However, when I convert the table to a range, filtering starts working again

Simple test code shows this problem;

Data block as a Table
Table1
C D E
8 COL1 COL2 COL3
9 1 4 4
10 2 4 8
11 2 50 58
12 34 49 107
13 34 50 157
14 50 1 158
15 2 160

NOTES
1 Table1 defined as C8:E15
2 COL3 maintains a running total of COL2 i.e
E9: =SUBTOTAL(9,$D$8:D9)
The formula in COL3 breaks filtering on the table


The option of converting the table into a range has drawbacks especially if the data is going to be accessed using VBA code. One loses the very real advantages of having a table object.

The other workaround is to position the Running Total column (COL3) as the first column to the right of the table. Filtering works fine as long as this column is not part of the table definition. There is no way to exclude the running total column from the table definition if it is positioned anywhere between the table's first column and last column.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2010 Pivot Table carl Excel Programming 2 July 12th 12 03:46 PM
Pivot Table Excel 2010 Meni Excel Programming 0 May 7th 11 04:21 AM
Excel graph function is broken Keith Farnham Charts and Charting in Excel 1 September 9th 05 10:28 PM
subtotal - pivot table - or better function klafert Excel Worksheet Functions 0 June 16th 05 06:12 AM
Excel's Pivot Table & Subtotal function should have a median fie. Mary Excel Worksheet Functions 1 December 3rd 04 04:27 PM


All times are GMT +1. The time now is 04:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"