Assigning numbers to selected rows

Occasional Contributor

I have a file with about 400 rows which I have set up to allow me to select using filters on any of the columns.  I need to assign sequential numbers, starting with "1",  to the selected rows.  I can't figure out how to number ONLY the selected rows without human intervention. 

 

In other words, if my filter selects the 40th through the 60th rows of my file, how can I number only those rows, starting with "1"?

4 Replies

@NorskGrandpa 

Let's say that you want the sequence numbers in column A.

Select the visible (filtered) cells in column A.

In the following, I will assume that the first cell in the selection is A40.

Enter the formula

 

=MAX($A$1:$A39)+1

 

where 39 is the row number of the cell above the first cell in the selection.

Confirm the formula with Ctrl+Enter so that it is entered in all selected cells.

Unless I misunderstand, this appears to require human intervention to enter the address of the cell before the selected cell. I would like to programmatically identify the first selected row and start numbering there -- in advance I would not know which row would be the first selected.

@NorskGrandpa 

Try this:

=SUM(A1,SUBTOTAL(3,B2))

Row 1 contains the headers. The formula starts in A2.

 

I didn't describe the problem adequately, however the responses I received got me thinking of a solution which worked. Thanks for jogging my thought process out of the rut it was in>