Auto fill / sequential numbering in a filtered column

**Kaycee831** · 05-22-2018, 07:12 AM

hi.. apologies if this has been covered somewhere else before, but having done various searches for any existing threads, i couldn't find anything quickly that didn't involve trawling through over 163 pages.

i have a spreadsheet which when filtered, i want to use the 'autofill/sequential numbering' option in a column. It works when it's not filtered (ie I insert 1/2 and then double click the square which will then autofill the remaining rows in the column), but doesn't work with a filtered range. Is there a way to do this?

ie:
1
2
3
4
5
6
etc....

if someone has seen this topic covered before and has the link, please do provide that and feel free to close this one off.

thank you
Kaycee

**samba_ravi** · 05-22-2018, 07:51 AM

Assuming the auto filter required from A2 try the above and copy towards down

a2=SUBTOTAL(103,B$2:B2)+0
Note: If your filtered column is not B then change B$2:B2 as one of your filtered column
if your filtered column is H the change the formula as =SUBTOTAL(103,H$2:H2)+0

**Kaycee831** · 05-22-2018, 09:08 AM

hi
thanks, but this does not work.

The column is B with the first filtered row being 3 through to row 93.
Having changed your formula to: =SUBTOTAL(103,B$3:B3)+0, it returns a 0 value.

**JeteMc** · 05-26-2018, 09:09 PM

Here is my interpretation of Samba's solution and it seems to work quite well. Test by changing the filter for column B.
If it is not working for you I would suggest uploading a sample of your file by clicking on GO ADVANCED button below the Quick Reply window and then scrolling down to Manage Attachments to open the upload window.
Let us know if you have any questions.