Imagine a 3x5 table as follows, starting in cell A1:
RowNumber; Region; SalesValue
1;North;$20
2;North;$11
3;South;$44
4;North;$29
5;South;$11
Place the cursor in cell A1 and select "Filter".
Everything works fine, Excel correctly detects 3 cols and 6 rows (inc. header).
Now make the row numbers 'dynamic' as follows:
Replace the hard coded row number in A2 with the following formula:
=SUBTOTAL(3,$C$2:C2), then copy down.
The RowNumbers are now 'dynamic', i.e. they are always consecutive, even when filtered.
Place the cursor in cell A1 and select "Filter".
Excel detects 3 cols and ONLY 5 ROWS (inc. header).
The last row is never detected.
Manually defining the list range (Advanced) doesn't work either.
Excel readjusts the range to exclude the last row,
Surely this is a bug?
Bookmarks