Indicate Filtered in XL03

[drbrow592]

Is there a way to inidcate that a column is fitlered, such as shading the header when filtered, and then unshade when not filtere?

I have about 50 columns in spreadsheet, all with filters and several users may filter. I would like to have some indicator that shows if a column is filter and then when not filtered.

 

[VoG]

You could use this UDF to display what the filter criteria are http://www.mrexcel.com/forum/showthread.php?t=347226

 

[jimbojones]

If you don't want to use VBA, and you have a column that contains values then you could use conditional formatting to compare sum formula with subtotal formula and shade cell when not equal. Assuming filter applies to rows 11:25 then something like this works for me:

=SUM($A$11:$A$25)<>SUBTOTAL(9,$A$11:$A$25)

 

[drbrow592]

jimbojones,

Where would I put this conditional format that you have so graciously provided??

 

[jimbojones]

Click on the header cell where the filter is applied (or wherever you wish the colour to be displayed) and then go to the menus: Format --> Conditional FOrmating. Change the condition from "Cell Value" to "Formula is" and stick the conditional formula in that dialog box.


Just re-reading your post, actually I'm not sure it would do exactly what you are looking for as you need the column highlighted! Teach me to read twice and think once!

 

[drbrow592]

James,

This sounds simple enough but the shading is not showing for some reason. I added the formula "=SUM($L$2:$L$1129)<>SUBTOTAL(9,$L$2:$L$1129)" to the header on column L and changed the conditional format to a shading. But nothing happens when I filter the column. Any ideas?

 

[jimbojones]

When I first entered my conditional format formula it automatically placed quotes around the formula. When I removed them it worked fine so maybe just have a play with it. I assume column L contains values. However, as I say above, not sure this answer achieves what you are looking for. Sorry. The UDF is probably much better

 

[drbrow592]

James,

Thanks for the help. I will continue to work with this and hopefully I can get it to work. Honestly I don't know much about VBA or UDF, so this was the better chance for me.

Thanks again

 

[jimbojones]

An alternative, possibly simpler approach, (although maybe a little bit of a work around) would be to have a button on the worksheet that a user could press to unfilter all data. If you know how to write a very basic macro and are familiar with navigating the Visual Basic editor then placing this macro in your spreadsheet would enable all filtered data to be shown:

Sub Unfilter_All()
On Error Resume Next
ActiveSheet.ShowAllData
End Sub


You could even insert this in a workbook_open event so the data would always be unfiltered when the workbook is opened by a user. Although doing this you may need to change activesheet to Sheets("Sheet1").ShowAllData

Good luck

 

[drbrow592]

James,

I did get your Conditional Format to work for one column filter with "=COUNTA(L$2:L$1128)<>SUBTOTAL(3,L$2:L$1128)", but all columns shade the same with one filter when I paint that format to other columns.

I would like to have only the column heading that is filtered to shade and other column headings to shade only when they are filtered. Is this possible with out a lot of work??

Don