Automatically updating a filter

jwgreen1986

Board Regular
Joined
Mar 8, 2019
Messages
64
I am having issues with a filter automatically updating. I have a set range of cells where a sales league table updates. i have 198 positions in the league table incase of increases to staff. i then have a little formula to the right of this that i will hide. i was hoping it would be a case of using this to show or hide values in my league table. its a simple formula =IFERROR(IF(E6="",0,1),"NA"). i was hoping that this will then have a 0 when there is nothing in the coresponding cell and 1 when there was something in it. the formula works so it has 1's and 0's depending on whether or not there is something in the cell. i was then hoping i could simply apply a filter on the 1's so it would basically only show stuff in the league table that is not empty.

unfortunately though if i change the table by a couple of drop downs i have (changes the month or certain conditions) this then changes the amount of people that appear in my league table. so if there is an increase of people in one month to the other it doesnt actually increase the amount of people it shows in the league table. similarly if the amount decreases, it will make the cells blank in the league table, but doesnt hide them.

does anyone know a way i can get this to work. all i want my league table to do is increase if there has been more people selling one month, and then hide blank cells if there was a decrease one month.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,026
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
There are a couple of ways. In the Sort & Filter option the is a Reapply option. This will refresh your filters with their current setting. To fully automate it, you could create a change event procedure like this to reapply filters every time something changes.
Code:
<code class="vb keyword">Private</code> <code class="vb keyword">Sub</code> <code class="vb plain">Worksheet_Change(</code><code class="vb keyword">ByVal</code> <code class="vb plain">Target </code><code class="vb keyword">As</code> <code class="vb plain">Range)</code>
<code class="vb spaces">   </code><code class="vb plain">Sheets(</code><code class="vb string">"Sheet1"</code><code class="vb plain">).AutoFilter.ApplyFilter</code>

<code class="vb keyword">End</code> <code class="vb keyword">Sub</code>
 

jwgreen1986

Board Regular
Joined
Mar 8, 2019
Messages
64
so with the code you have supplied, is it a case of changing the sheet1 to that my sheet is called and then that is it?
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,026
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Yes sorry I should have made that clear. Also, this code needs to be in the worksheet vba area. To put it there, right-click on the worksheet name at the bottom ans select View Code. Then just paste this code there.
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,026
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
You're welcome - and thanks for the votes too.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,033
Messages
5,526,373
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top