numbering filtered rows

[glen.e.mettler]

If I put = Row() in a column of cells I get the row number.
Now, suppose I filter the data. I want to show the sequence row number
of the filtered data so that it shows 1,2,3,4,5 etc regardless of the
actual row number.
For example, I amy have "Joe Smith" on row #45 but when I filter on a
particular parameter, "Joe Smith" may be on row 2 of the filtered data.
That row is what I need to show.

How can I do that? Is it possible without numbering by hand?

Glen

 

[Dave Peterson]

If you can find a column that always has data in it (like that name column), you
could use:

in A2 (with headers in Row 1 and names in column B):

=subtotal(3,$b$2:b2)

and drag down column A.

 

[Guest]

If you have one filtered column that will always have a value (no blanks),
maybe this will work for you:

Assuming your data list is in A10:Z1000 and Col_B will have no blanks

Insert a column in front of your data for the sequence numbers and head it
Seq.
Now Col_A is the Seq column

A11: =SUBTOTAL(3,B$10:B11)-1
Copy that formula down through A1000

When you apply a filter, the Seq column will automatically number records,
beginning with the first visible record.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Debra Dalgleish]

But don't use this technique in conjunction with the Data>Subtotals
command, or it may delete all the rows in your table!

 

[glen.e.mettler]

Thanks Dave, just what I needed.

Glen

 

[Dave Peterson]

I'm confused...

How would this delete any of the rows?

 

[Debra Dalgleish]

When you remove the Subtotals (Data>Subtotals, Remove All), every row in
the table that contains a Subtotal formula is deleted, including
Subtotal formulas that were manually inserted.

And there's no Undo. You can close the file without saving the changes,
which is okay, unless you've made lots of other changes, and hadn't
saved them.

I'm confused...

How would this delete any of the rows?

 

[Dave Peterson]

Ahhhh.

Now I see (as I picked up that hammer and saw!).

When you remove the Subtotals (Data>Subtotals, Remove All), every row in
the table that contains a Subtotal formula is deleted, including
Subtotal formulas that were manually inserted.

And there's no Undo. You can close the file without saving the changes,
which is okay, unless you've made lots of other changes, and hadn't
saved them.