The formula will work with or without the space.) (I left a space between the minus signs below, just for clarity. Dick Kusleika was subtotalling filtered rows, and discovered that he could fix the problem by typing two minus signs in front of the SUBTOTAL function. Today I found another workaround, that’s much better than the dummy row solution. ![]() Workaround 2: Change the SUBTOTAL formula There’s no date in row 10, so it doesn’t affect the record numbering. N the screenshot below, the SUBTOTAL function is copied down to row 10, and when the AutoFilter is applied, row 9 is hidden. Then, that row is treated as the last row, and all the real data is shown or hidden, based on the AutoFilter criteria. Usually, I add a dummy row at the end of the list, with just the SUBTOTAL function, or other dummy data. So, if you want your last row hidden when using the SUBTOTAL function in a filtered list, you can use one of the following workarounds. If there’s a SUBTOTAL function in the last row of that database, Excel decides that it’s a special row for the list’s totals, and it’s not included in the named range. Using Jan Karel Pieterse’s Name Manager utility, I can see the definition for Orders!_FilterDatabase.Įven though the list ends in row 9, the named range stops at row 8: =Orders!$B$1:$H$8 When you use an AutoFilter, Excel creates a hidden named range for the database. Also, its row number isn’t blue, like the row numbers for rows 2, 3, 5 and 8. Can you spot the problem?Īlthough the list is filtered for Paper or Staplers, the File Folders record in row 9 is also visible. In the screenshot below, the list is filtered for the products Paper or Staplers, as you can see in the AutoFilter tooltip. The SUBTOTAL function works well, and renumbers our rows as expected, but there’s something wrong. Now, when you apply an Excel AutoFilter, or show all the records, your record numbers in column B will change. ![]() We want the count to end at the row the formula is in.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |