Automatically updating a filter

[jwgreen1986]

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.

 

[jmacleary]

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 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]

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]

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.

 

[jwgreen1986]

Brilliant,

thanks this is now working beautifully

 

[jmacleary]

You're welcome - and thanks for the votes too.