Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Autofilter Bug

Status
Not open for further replies.

adgish

Programmer
Joined
Jun 6, 2003
Messages
5
Location
US
Hi guys,

I recently ran into a problem with the autofilter in Excel (specifically its interaction with data series and scatterplot charts). After experimenting with it more, I now understand the problem but still have no solution. Here is what I know:

When data is autofiltered, the series containing it is temporarily “cropped” down to a size that contains only data points that meet the requirements of the filter. Thus, only the filtered points are shown on a scatterplot chart.

However, although the series is shortened, Excel doesn’t pick and choose the points to cut out, it simply chops the correct number off the end of the series. This means that points which meet the requirements of the filter are shown in the correct spot on a scatterplot, but their markerstyles and labels correspond to other points.

For example, imagine a 10 point series with data labels 1 through 10. If I filter out points 8 and 9, they will show up in the correct position on my scatter plot chart, but they will be labeled 1 and 2 respectively. Also, they will have the markerstyles of points 1 and 2, rather than their own.

I need the data points to maintain their correct data labels and markerstyles(symbols) even when filtered. I would appreciate any insight you can offer.

-Andrew
 
This is due to the graph not knowing that the range sizes have changed.

Try seting the ranges up be to be dynamic as shown in and you may have better luck.

Please note that COUNTA does not change with hidden/filtered items bu if you use SUBTOTAL(3,range) you will get the correct result.

-----------------
SUBTOTAL - help file extract

See Also



Returns a subtotal in a list or database. It is generally easier to create a list with subtotals using the Subtotals command (Data menu). Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.

Syntax

SUBTOTAL(function_num,ref1,ref2,…)



Function_num is the number 1 to 11 that specifies which function to use in calculating subtotals within a list.



Function_Num Function

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP


Ref1, ref2, are 1 to 29 ranges or references for which you want the subtotal.

Remarks

· If there are other subtotals within ref1, ref2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting.
· SUBTOTAL will ignore any hidden rows that result from a list being filtered. This is important when you want to subtotal only the visible data that results from a list that you have filtered.
· If any of the references are 3-D references, SUBTOTAL returns the #VALUE! error value.



Example

SUBTOTAL(9,C3:C5) will generate a subtotal of the cells C3:C5 using the SUM function
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top