numbering filtered rows

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

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.

"glen.e.mettler@lmco.com" wrote:
>
> 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 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


"glen.e.mettler@lmco.com" wrote:

> 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
>
>

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

Dave Peterson wrote:
> 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.
>
> "glen.e.mettler@lmco.com" wrote:
>
>>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
>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thanks Dave, just what I needed.

Glen

I'm confused...

How would this delete any of the rows?

Debra Dalgleish wrote:
>
> But don't use this technique in conjunction with the Data>Subtotals
> command, or it may delete all the rows in your table!
>
> Dave Peterson wrote:
> > 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.
> >
> > "glen.e.mettler@lmco.com" wrote:
> >
> >>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
> >
> >
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html

--

Dave Peterson

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.

Dave Peterson wrote:
> I'm confused...
>
> How would this delete any of the rows?
>
> Debra Dalgleish wrote:
>
>>But don't use this technique in conjunction with the Data>Subtotals
>>command, or it may delete all the rows in your table!
>>
>>Dave Peterson wrote:
>>
>>>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.
>>>
>>>"glen.e.mettler@lmco.com" wrote:
>>>
>>>
>>>>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
>>>
>>>
>>--
>>Debra Dalgleish
>>Contextures
>>http://www.contextures.com/tiptech.html
>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Ahhhh.

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



Debra Dalgleish wrote:
>
> 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.
>
> Dave Peterson wrote:
> > I'm confused...
> >
> > How would this delete any of the rows?
> >
> > Debra Dalgleish wrote:
> >
> >>But don't use this technique in conjunction with the Data>Subtotals
> >>command, or it may delete all the rows in your table!
> >>
> >>Dave Peterson wrote:
> >>
> >>>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.
> >>>
> >>>"glen.e.mettler@lmco.com" wrote:
> >>>
> >>>
> >>>>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
> >>>
> >>>
> >>--
> >>Debra Dalgleish
> >>Contextures
> >>http://www.contextures.com/tiptech.html
> >
> >
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html

--

Dave Peterson