Position | Sales Person | Sales $ |
=SUBTOTAL(3,B$2:B2) | DAVE | 1000 |
=SUBTOTAL(3,B$2:B3)<strike></strike> | MARK | 2000 |
=SUBTOTAL(3,B$2:B4)<strike></strike> | HELEN | 3000 |
=SUBTOTAL(3,B$2:B5)<strike></strike> | MATT | 4000 |
=SUBTOTAL(3,B$2:B6)<strike></strike><strike></strike> | DEREK | 5000 |
=SUBTOTAL(3,B$2:B7)<strike></strike><strike></strike> | MARY | 6000 |
=SUBTOTAL(3,B$2:B8)<strike></strike><strike></strike> | MARTHA | 7000 |
<tbody>
</tbody>
I have replicated my Excel 2013 Table above. When I use the SubTotal function to automatically renumber the rows from 1 to the last, it is causing an issue when I filter the Sales column.
When I apply a Top 5 number filter to the Sales column, the filter doesn't work and returns a 6th row of data. (Apply a Top 10 and you'll get 11 rows of data)
I have tested my Excel table without the SubTotal function it and it all works as it should do.
I really need the table to renumber the position column itself automatically from 1 to the last - after applying filters which may add or remove rows of data.
Is there an alternative to the SubTotal function - which is causing my Filter errors?