I have a sheet that I auto filter by job title, i.e. Doctor, Nurse, Tech,
Admin.

With no auto filter on, the each person list listed by name, with the column
next to their name designating them as 1 through 100.

If I auto filter for, let's say Nurse, of which there are 35, what formula
can I put in the cell to the right of the name to number these people as 1
through 35?

In other words, the number would change depending on which rows were visible
at the time.

The names on this list change on a regular basis.

This may not be what you want.

Apply a filter for say Nurses. In cell to the right of the 1st name - say
in row 2 enter 1 and in the cell under it enter the formula:

=A2+1

Now copy this formula down the visible cells using the fill handle.

Do the same with each job title. Copying down using the fill handle copies
only to visible cells so the formulas will only relate to the appropriate
job title.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"carrera" wrote in message
...
I have a sheet that I auto filter by job title, i.e. Doctor, Nurse, Tech,
Admin.

With no auto filter on, the each person list listed by name, with the
column
next to their name designating them as 1 through 100.

If I auto filter for, let's say Nurse, of which there are 35, what formula
can I put in the cell to the right of the name to number these people as 1
through 35?

In other words, the number would change depending on which rows were
visible
at the time.

The names on this list change on a regular basis.

hmm...when I filter for nurses, the rows #'s are not going to be
sequential...for instance, the rows #'s read 5, 18, 24, 25, 32 etc...
so putting in A2 + 1 doesn't work, If I don't filter, and use that formula,
then filter, the numbers in the row are not sequential either.

I see what you are saying, but how do you get a formula to apply only to the
visible rows?

"Sandy Mann" wrote:

This may not be what you want.

Apply a filter for say Nurses. In cell to the right of the 1st name - say
in row 2 enter 1 and in the cell under it enter the formula:

=A2+1

Now copy this formula down the visible cells using the fill handle.

Do the same with each job title. Copying down using the fill handle copies
only to visible cells so the formulas will only relate to the appropriate
job title.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"carrera" wrote in message
...
I have a sheet that I auto filter by job title, i.e. Doctor, Nurse, Tech,
Admin.

With no auto filter on, the each person list listed by name, with the
column
next to their name designating them as 1 through 100.

If I auto filter for, let's say Nurse, of which there are 35, what formula
can I put in the cell to the right of the name to number these people as 1
through 35?

In other words, the number would change depending on which rows were
visible
at the time.

The names on this list change on a regular basis.

=SUBTOTAL(103,$B$2:B2)

Enter this formula for the first person, where B is the column that contains
the name and the first person appears in row 2. Adjust as necessary. Copy
down for the entire list, will properly display current count when filtered...
Good luck!

"carrera" wrote:

hmm...when I filter for nurses, the rows #'s are not going to be
sequential...for instance, the rows #'s read 5, 18, 24, 25, 32 etc...
so putting in A2 + 1 doesn't work, If I don't filter, and use that formula,
then filter, the numbers in the row are not sequential either.

I see what you are saying, but how do you get a formula to apply only to the
visible rows?

"Sandy Mann" wrote:

This may not be what you want.

Apply a filter for say Nurses. In cell to the right of the 1st name - say
in row 2 enter 1 and in the cell under it enter the formula:

=A2+1

Now copy this formula down the visible cells using the fill handle.

Do the same with each job title. Copying down using the fill handle copies
only to visible cells so the formulas will only relate to the appropriate
job title.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"carrera" wrote in message
...
I have a sheet that I auto filter by job title, i.e. Doctor, Nurse, Tech,
Admin.

With no auto filter on, the each person list listed by name, with the
column
next to their name designating them as 1 through 100.

If I auto filter for, let's say Nurse, of which there are 35, what formula
can I put in the cell to the right of the name to number these people as 1
through 35?

In other words, the number would change depending on which rows were
visible
at the time.

The names on this list change on a regular basis.

Sorry, it seem to work for 20 odd rows then goes wrong and I only tested on
18 rows. Use BonM's formula.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"carrera" wrote in message
...
hmm...when I filter for nurses, the rows #'s are not going to be
sequential...for instance, the rows #'s read 5, 18, 24, 25, 32 etc...
so putting in A2 + 1 doesn't work, If I don't filter, and use that
formula,
then filter, the numbers in the row are not sequential either.

I see what you are saying, but how do you get a formula to apply only to
the
visible rows?

"Sandy Mann" wrote:

This may not be what you want.

Apply a filter for say Nurses. In cell to the right of the 1st name -
say
in row 2 enter 1 and in the cell under it enter the formula:

=A2+1

Now copy this formula down the visible cells using the fill handle.

Do the same with each job title. Copying down using the fill handle
copies
only to visible cells so the formulas will only relate to the appropriate
job title.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"carrera" wrote in message
...
I have a sheet that I auto filter by job title, i.e. Doctor, Nurse,
Tech,
Admin.

With no auto filter on, the each person list listed by name, with the
column
next to their name designating them as 1 through 100.

If I auto filter for, let's say Nurse, of which there are 35, what
formula
can I put in the cell to the right of the name to number these people
as 1
through 35?

In other words, the number would change depending on which rows were
visible
at the time.

The names on this list change on a regular basis.

Oh!
How stupid of me, that should have been obvious!
Thanks so much.

"BoniM" wrote:

=SUBTOTAL(103,$B$2:B2)

Enter this formula for the first person, where B is the column that contains
the name and the first person appears in row 2. Adjust as necessary. Copy
down for the entire list, will properly display current count when filtered...
Good luck!

"carrera" wrote:

hmm...when I filter for nurses, the rows #'s are not going to be
sequential...for instance, the rows #'s read 5, 18, 24, 25, 32 etc...
so putting in A2 + 1 doesn't work, If I don't filter, and use that formula,
then filter, the numbers in the row are not sequential either.

I see what you are saying, but how do you get a formula to apply only to the
visible rows?

"Sandy Mann" wrote:

This may not be what you want.

Apply a filter for say Nurses. In cell to the right of the 1st name - say
in row 2 enter 1 and in the cell under it enter the formula:

=A2+1

Now copy this formula down the visible cells using the fill handle.

Do the same with each job title. Copying down using the fill handle copies
only to visible cells so the formulas will only relate to the appropriate
job title.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"carrera" wrote in message
...
I have a sheet that I auto filter by job title, i.e. Doctor, Nurse, Tech,
Admin.

With no auto filter on, the each person list listed by name, with the
column
next to their name designating them as 1 through 100.

If I auto filter for, let's say Nurse, of which there are 35, what formula
can I put in the cell to the right of the name to number these people as 1
through 35?

In other words, the number would change depending on which rows were
visible
at the time.

The names on this list change on a regular basis.

Try the 'COUNTIF' function. On the test data I used, I inserted a row above
my filtered headings and entered this formula above the column I wanted to
count:
COUNTIF(F:F,F3)
This counted my filtered rows. Note: You must have the 'Iteration' button
selected at Tools/Options/Calculation or you will get the 'circular
reference' error.

Now that you have the rows counted, you can refer to this cell to create a
formula to number the displayed rows and fill down. In my case I put
'=$F$1-(F1-1)' in the first cell and then '=$F$1-($F$1-1)+O3' in the second
cell and thereafter. If desired, you would need to add to the formula to
check that the max count was not exceeded.

--
T Tipsy


"carrera" wrote:

I have a sheet that I auto filter by job title, i.e. Doctor, Nurse, Tech,
Admin.

With no auto filter on, the each person list listed by name, with the column
next to their name designating them as 1 through 100.

If I auto filter for, let's say Nurse, of which there are 35, what formula
can I put in the cell to the right of the name to number these people as 1
through 35?

In other words, the number would change depending on which rows were visible
at the time.

The names on this list change on a regular basis.

Using your idea:

=COUNTIF($A$2:$A$128,A2)-COUNTIF(A2:A128,A2)+1

Copied down all produces the numbers list when filtered.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Terry Tipsy" wrote in message
...
Try the 'COUNTIF' function. On the test data I used, I inserted a row
above
my filtered headings and entered this formula above the column I wanted to
count:
COUNTIF(F:F,F3)
This counted my filtered rows. Note: You must have the 'Iteration'
button
selected at Tools/Options/Calculation or you will get the 'circular
reference' error.

Now that you have the rows counted, you can refer to this cell to create a
formula to number the displayed rows and fill down. In my case I put
'=$F$1-(F1-1)' in the first cell and then '=$F$1-($F$1-1)+O3' in the
second
cell and thereafter. If desired, you would need to add to the formula to
check that the max count was not exceeded.

--
T Tipsy


"carrera" wrote:

I have a sheet that I auto filter by job title, i.e. Doctor, Nurse, Tech,
Admin.

With no auto filter on, the each person list listed by name, with the
column
next to their name designating them as 1 through 100.

If I auto filter for, let's say Nurse, of which there are 35, what
formula
can I put in the cell to the right of the name to number these people as
1
through 35?

In other words, the number would change depending on which rows were
visible
at the time.

The names on this list change on a regular basis.