szchris384
New member
- Joined
- Mar 2, 2014
- Messages
- 8
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- 16.0.12827.20235 64bit
Hi, I'm needing help with creating a counter column within a table that has a filter(s).
The counter needs to reset after 25 back to 1.
Essentially creating a counter system that goes from 1-25, 1-25 1-25, etc. on the filtered table.
If the filters are not active, the formula is straight forward.
=IF(INDIRECT("B"&ROW()-1)="25","01", TEXT(INDIRECT("B"&ROW()-1)+1,"0#"))
If the filters are active the counts include the filtered (hidden) rows. Resulting in 17, 19, 19, 25, 09, 19, etc.
With the filter active, I can use the Subtotal function to get a counter system skipping the filtered out items.
=SUBTOTAL(3,C$8:C8) (table starts on Row 8) (col C is text and always has a value in it)
But this will do the whole table from 1-last entry (that meet the filter criteria)
I would like the counter to go from 1-25, then 1-25, etc. on the filtered table
Any help would be appreciated
400+ records in the table
several different filter options
VBA is not an option.
Thanks
Chris
The counter needs to reset after 25 back to 1.
Essentially creating a counter system that goes from 1-25, 1-25 1-25, etc. on the filtered table.
If the filters are not active, the formula is straight forward.
=IF(INDIRECT("B"&ROW()-1)="25","01", TEXT(INDIRECT("B"&ROW()-1)+1,"0#"))
If the filters are active the counts include the filtered (hidden) rows. Resulting in 17, 19, 19, 25, 09, 19, etc.
With the filter active, I can use the Subtotal function to get a counter system skipping the filtered out items.
=SUBTOTAL(3,C$8:C8) (table starts on Row 8) (col C is text and always has a value in it)
But this will do the whole table from 1-last entry (that meet the filter criteria)
I would like the counter to go from 1-25, then 1-25, etc. on the filtered table
Any help would be appreciated
400+ records in the table
several different filter options
VBA is not an option.
Thanks
Chris