Creating a numbered list on a filtered worksheet
 

Hi all

I have a large worksheet and have set an AutoFilter on the data, to display
the information I need, however, I now need to insert a new column (A) and
number the entries on the filtered list as 1, 2, 3 etc.

When I enter 1 and 2, highlight them both and drag down, it doesn't fill in
with 3, 4 etc. it gives me a list of 1's. I am presuming this is because it
recognises the list is filtered.

Is there any way I can auto-number this filtered list?

THank you.
Louise

Creating a numbered list on a filtered worksheet
 

As far as I know there is no way of bypassing that, it's because Excel can
copy non contiguous cells and paste them in one solid block but it cannot do
it the other way around. So even if you use something that will fill like a
series it will
skip the filtered rows

=ROWS($A$1:A1)

copied down will return 1,2,3 and so on but if the first values in your
filtered list is in row 2 and second value is in row 5 it will fill like 1,
4 and so on



--
Regards,

Peo Sjoblom


"Louise" wrote in message
...
Hi all

I have a large worksheet and have set an AutoFilter on the data, to
display
the information I need, however, I now need to insert a new column (A) and
number the entries on the filtered list as 1, 2, 3 etc.

When I enter 1 and 2, highlight them both and drag down, it doesn't fill
in
with 3, 4 etc. it gives me a list of 1's. I am presuming this is because
it
recognises the list is filtered.

Is there any way I can auto-number this filtered list?

THank you.
Louise

Creating a numbered list on a filtered worksheet
 

Hi, thanks for your reply.

You've confirmed what I suspected, I just wondered if there was a way around
it.

Thanks any way.

Louise

"Peo Sjoblom" wrote:

As far as I know there is no way of bypassing that, it's because Excel can
copy non contiguous cells and paste them in one solid block but it cannot do
it the other way around. So even if you use something that will fill like a
series it will
skip the filtered rows

=ROWS($A$1:A1)

copied down will return 1,2,3 and so on but if the first values in your
filtered list is in row 2 and second value is in row 5 it will fill like 1,
4 and so on



--
Regards,

Peo Sjoblom


"Louise" wrote in message
...
Hi all

I have a large worksheet and have set an AutoFilter on the data, to
display
the information I need, however, I now need to insert a new column (A) and
number the entries on the filtered list as 1, 2, 3 etc.

When I enter 1 and 2, highlight them both and drag down, it doesn't fill
in
with 3, 4 etc. it gives me a list of 1's. I am presuming this is because
it
recognises the list is filtered.

Is there any way I can auto-number this filtered list?

THank you.
Louise

Creating a numbered list on a filtered worksheet
 

Louise,

Use this formula in cell A2:

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

and copy down to match your list. It will update when filtered to always show a correctly numbered
list.

HTH,
Bernie
MS Excel MVP


"Louise" wrote in message
...
Hi all

I have a large worksheet and have set an AutoFilter on the data, to display
the information I need, however, I now need to insert a new column (A) and
number the entries on the filtered list as 1, 2, 3 etc.

When I enter 1 and 2, highlight them both and drag down, it doesn't fill in
with 3, 4 etc. it gives me a list of 1's. I am presuming this is because it
recognises the list is filtered.

Is there any way I can auto-number this filtered list?

THank you.
Louise

Creating a numbered list on a filtered worksheet
 

Louise,

I should have stressed that you need to have all cells showing (the list unfiltered, or use show
all) prior to entering and copying the formula.

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Louise,

Use this formula in cell A2:

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

and copy down to match your list. It will update when filtered to always show a correctly
numbered list.

HTH,
Bernie
MS Excel MVP


"Louise" wrote in message
...
Hi all

I have a large worksheet and have set an AutoFilter on the data, to display
the information I need, however, I now need to insert a new column (A) and
number the entries on the filtered list as 1, 2, 3 etc.

When I enter 1 and 2, highlight them both and drag down, it doesn't fill in
with 3, 4 etc. it gives me a list of 1's. I am presuming this is because it
recognises the list is filtered.

Is there any way I can auto-number this filtered list?

THank you.
Louise

Creating a numbered list on a filtered worksheet
 

Louise เขียน:
Hi all

I have a large worksheet and have set an AutoFilter on the data, to display
the information I need, however, I now need to insert a new column (A) and
number the entries on the filtered list as 1, 2, 3 etc.

When I enter 1 and 2, highlight them both and drag down, it doesn't fill in
with 3, 4 etc. it gives me a list of 1's. I am presuming this is because it
recognises the list is filtered.

Is there any way I can auto-number this filtered list?

THank you.
Louise

Try this :

A2

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

Enter and copy down

Hope this helps.

Creating a numbered list on a filtered worksheet
 

Also if for some reason she would filter on blanks it won't work

--
Regards,

Peo Sjoblom



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Louise,

I should have stressed that you need to have all cells showing (the list
unfiltered, or use show all) prior to entering and copying the formula.

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Louise,

Use this formula in cell A2:

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

and copy down to match your list. It will update when filtered to always
show a correctly numbered list.

HTH,
Bernie
MS Excel MVP


"Louise" wrote in message
...
Hi all

I have a large worksheet and have set an AutoFilter on the data, to
display
the information I need, however, I now need to insert a new column (A)
and
number the entries on the filtered list as 1, 2, 3 etc.

When I enter 1 and 2, highlight them both and drag down, it doesn't fill
in
with 3, 4 etc. it gives me a list of 1's. I am presuming this is
because it
recognises the list is filtered.

Is there any way I can auto-number this filtered list?

THank you.
Louise

Creating a numbered list on a filtered worksheet
 

Also, don't include the numbered list in the filter.

Try this:

A1 = header = Count
B1 = header = Answer

B2:B5 = yes,no,yes,no

A2 = formula: =SUBTOTAL(3,B$2:B2) copied down to A5

Select B1 and apply the filter. Excel automatically includes column A in the
filter. Filter on "yes" in the Answer column. See what happens? The last
"no" is included in the filter.

Now, "show all", remove the filter. Reapply the filter selecting *only*
column B. Filter on "yes". See the difference?

Why does that happen?

Biff

"Peo Sjoblom" wrote in message
...
Also if for some reason she would filter on blanks it won't work

--
Regards,

Peo Sjoblom



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Louise,

I should have stressed that you need to have all cells showing (the list
unfiltered, or use show all) prior to entering and copying the formula.

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Louise,

Use this formula in cell A2:

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

and copy down to match your list. It will update when filtered to
always show a correctly numbered list.

HTH,
Bernie
MS Excel MVP


"Louise" wrote in message
...
Hi all

I have a large worksheet and have set an AutoFilter on the data, to
display
the information I need, however, I now need to insert a new column (A)
and
number the entries on the filtered list as 1, 2, 3 etc.

When I enter 1 and 2, highlight them both and drag down, it doesn't
fill in
with 3, 4 etc. it gives me a list of 1's. I am presuming this is
because it
recognises the list is filtered.

Is there any way I can auto-number this filtered list?

THank you.
Louise

Creating a numbered list on a filtered worksheet
 

Hi Biff

Comes out as 1 2 in both cases for me, with either 2 yes's showing or 2
no's.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Also, don't include the numbered list in the filter.

Try this:

A1 = header = Count
B1 = header = Answer

B2:B5 = yes,no,yes,no

A2 = formula: =SUBTOTAL(3,B$2:B2) copied down to A5

Select B1 and apply the filter. Excel automatically includes column A
in the filter. Filter on "yes" in the Answer column. See what happens?
The last "no" is included in the filter.

Now, "show all", remove the filter. Reapply the filter selecting
*only* column B. Filter on "yes". See the difference?

Why does that happen?

Biff

"Peo Sjoblom" wrote in message
...
Also if for some reason she would filter on blanks it won't work

--
Regards,

Peo Sjoblom



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Louise,

I should have stressed that you need to have all cells showing (the
list unfiltered, or use show all) prior to entering and copying the
formula.

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Louise,

Use this formula in cell A2:

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

and copy down to match your list. It will update when filtered to
always show a correctly numbered list.

HTH,
Bernie
MS Excel MVP


"Louise" wrote in message
...
Hi all

I have a large worksheet and have set an AutoFilter on the data,
to display
the information I need, however, I now need to insert a new column
(A) and
number the entries on the filtered list as 1, 2, 3 etc.

When I enter 1 and 2, highlight them both and drag down, it
doesn't fill in
with 3, 4 etc. it gives me a list of 1's. I am presuming this is
because it
recognises the list is filtered.

Is there any way I can auto-number this filtered list?

THank you.
Louise

Creating a numbered list on a filtered worksheet
 

Hmmm...

Here's what I get when column A (the formula column) is included in the
filter:

http://img339.imageshack.us/img339/751/filterafu4.jpg

Here's what I get when column A (the formula column) is not included in the
filter:

http://img339.imageshack.us/img339/2381/filterbid8.jpg

????

Biff

"Roger Govier" wrote in message
...
Hi Biff

Comes out as 1 2 in both cases for me, with either 2 yes's showing or 2
no's.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Also, don't include the numbered list in the filter.

Try this:

A1 = header = Count
B1 = header = Answer

B2:B5 = yes,no,yes,no

A2 = formula: =SUBTOTAL(3,B$2:B2) copied down to A5

Select B1 and apply the filter. Excel automatically includes column A in
the filter. Filter on "yes" in the Answer column. See what happens? The
last "no" is included in the filter.

Now, "show all", remove the filter. Reapply the filter selecting *only*
column B. Filter on "yes". See the difference?

Why does that happen?

Biff

"Peo Sjoblom" wrote in message
...
Also if for some reason she would filter on blanks it won't work

--
Regards,

Peo Sjoblom



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Louise,

I should have stressed that you need to have all cells showing (the
list unfiltered, or use show all) prior to entering and copying the
formula.

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Louise,

Use this formula in cell A2:

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

and copy down to match your list. It will update when filtered to
always show a correctly numbered list.

HTH,
Bernie
MS Excel MVP


"Louise" wrote in message
...
Hi all

I have a large worksheet and have set an AutoFilter on the data, to
display
the information I need, however, I now need to insert a new column
(A) and
number the entries on the filtered list as 1, 2, 3 etc.

When I enter 1 and 2, highlight them both and drag down, it doesn't
fill in
with 3, 4 etc. it gives me a list of 1's. I am presuming this is
because it
recognises the list is filtered.

Is there any way I can auto-number this filtered list?

THank you.
Louise

Creating a numbered list on a filtered worksheet
 

Hi Biff

I see what you mean.
I have other data in columns, and selected columns B:D before applying
Autofilter, so A wasn't included.
When I include A, I get the same result as you.

I have no explanation for this, and don't have time to investigate
tonight, but if I come up with anything tomorrow, I will post back.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Hmmm...

Here's what I get when column A (the formula column) is included in
the filter:

http://img339.imageshack.us/img339/751/filterafu4.jpg

Here's what I get when column A (the formula column) is not included
in the filter:

http://img339.imageshack.us/img339/2381/filterbid8.jpg

????

Biff

"Roger Govier" wrote in message
...
Hi Biff

Comes out as 1 2 in both cases for me, with either 2 yes's showing or
2 no's.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Also, don't include the numbered list in the filter.

Try this:

A1 = header = Count
B1 = header = Answer

B2:B5 = yes,no,yes,no

A2 = formula: =SUBTOTAL(3,B$2:B2) copied down to A5

Select B1 and apply the filter. Excel automatically includes column
A in the filter. Filter on "yes" in the Answer column. See what
happens? The last "no" is included in the filter.

Now, "show all", remove the filter. Reapply the filter selecting
*only* column B. Filter on "yes". See the difference?

Why does that happen?

Biff

"Peo Sjoblom" wrote in message
...
Also if for some reason she would filter on blanks it won't work

--
Regards,

Peo Sjoblom



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Louise,

I should have stressed that you need to have all cells showing
(the list unfiltered, or use show all) prior to entering and
copying the formula.

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Louise,

Use this formula in cell A2:

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

and copy down to match your list. It will update when filtered
to always show a correctly numbered list.

HTH,
Bernie
MS Excel MVP


"Louise" wrote in message
...
Hi all

I have a large worksheet and have set an AutoFilter on the data,
to display
the information I need, however, I now need to insert a new
column (A) and
number the entries on the filtered list as 1, 2, 3 etc.

When I enter 1 and 2, highlight them both and drag down, it
doesn't fill in
with 3, 4 etc. it gives me a list of 1's. I am presuming this
is because it
recognises the list is filtered.

Is there any way I can auto-number this filtered list?

THank you.
Louise

Creating a numbered list on a filtered worksheet
 

I answered this same question a few days ago in the .misc group. I was
"playing" around with it and discovered this behavior.

Biff

"Roger Govier" wrote in message
...
Hi Biff

I see what you mean.
I have other data in columns, and selected columns B:D before applying
Autofilter, so A wasn't included.
When I include A, I get the same result as you.

I have no explanation for this, and don't have time to investigate
tonight, but if I come up with anything tomorrow, I will post back.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Hmmm...

Here's what I get when column A (the formula column) is included in the
filter:

http://img339.imageshack.us/img339/751/filterafu4.jpg

Here's what I get when column A (the formula column) is not included in
the filter:

http://img339.imageshack.us/img339/2381/filterbid8.jpg

????

Biff

"Roger Govier" wrote in message
...
Hi Biff

Comes out as 1 2 in both cases for me, with either 2 yes's showing or 2
no's.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Also, don't include the numbered list in the filter.

Try this:

A1 = header = Count
B1 = header = Answer

B2:B5 = yes,no,yes,no

A2 = formula: =SUBTOTAL(3,B$2:B2) copied down to A5

Select B1 and apply the filter. Excel automatically includes column A
in the filter. Filter on "yes" in the Answer column. See what happens?
The last "no" is included in the filter.

Now, "show all", remove the filter. Reapply the filter selecting *only*
column B. Filter on "yes". See the difference?

Why does that happen?

Biff

"Peo Sjoblom" wrote in message
...
Also if for some reason she would filter on blanks it won't work

--
Regards,

Peo Sjoblom



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Louise,

I should have stressed that you need to have all cells showing (the
list unfiltered, or use show all) prior to entering and copying the
formula.

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Louise,

Use this formula in cell A2:

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

and copy down to match your list. It will update when filtered to
always show a correctly numbered list.

HTH,
Bernie
MS Excel MVP


"Louise" wrote in message
...
Hi all

I have a large worksheet and have set an AutoFilter on the data, to
display
the information I need, however, I now need to insert a new column
(A) and
number the entries on the filtered list as 1, 2, 3 etc.

When I enter 1 and 2, highlight them both and drag down, it doesn't
fill in
with 3, 4 etc. it gives me a list of 1's. I am presuming this is
because it
recognises the list is filtered.

Is there any way I can auto-number this filtered list?

THank you.
Louise

Creating a numbered list on a filtered worksheet
 

It only seems to do this when there is a subtotal formula in column A. XL is
excluding the last row from the filtered list. Constants or other formulae
don't appear to cause a problem. Perhaps XL is assuming this last row is
subtotaling the list and s/b left visible after the filter is applied and is
not part of the actual data (due to there being a subtotal formula).


"T. Valko" wrote:

I answered this same question a few days ago in the .misc group. I was
"playing" around with it and discovered this behavior.

Biff

"Roger Govier" wrote in message
...
Hi Biff

I see what you mean.
I have other data in columns, and selected columns B:D before applying
Autofilter, so A wasn't included.
When I include A, I get the same result as you.

I have no explanation for this, and don't have time to investigate
tonight, but if I come up with anything tomorrow, I will post back.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Hmmm...

Here's what I get when column A (the formula column) is included in the
filter:

http://img339.imageshack.us/img339/751/filterafu4.jpg

Here's what I get when column A (the formula column) is not included in
the filter:

http://img339.imageshack.us/img339/2381/filterbid8.jpg

????

Biff

"Roger Govier" wrote in message
...
Hi Biff

Comes out as 1 2 in both cases for me, with either 2 yes's showing or 2
no's.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Also, don't include the numbered list in the filter.

Try this:

A1 = header = Count
B1 = header = Answer

B2:B5 = yes,no,yes,no

A2 = formula: =SUBTOTAL(3,B$2:B2) copied down to A5

Select B1 and apply the filter. Excel automatically includes column A
in the filter. Filter on "yes" in the Answer column. See what happens?
The last "no" is included in the filter.

Now, "show all", remove the filter. Reapply the filter selecting *only*
column B. Filter on "yes". See the difference?

Why does that happen?

Biff

"Peo Sjoblom" wrote in message
...
Also if for some reason she would filter on blanks it won't work

--
Regards,

Peo Sjoblom



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Louise,

I should have stressed that you need to have all cells showing (the
list unfiltered, or use show all) prior to entering and copying the
formula.

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Louise,

Use this formula in cell A2:

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

and copy down to match your list. It will update when filtered to
always show a correctly numbered list.

HTH,
Bernie
MS Excel MVP


"Louise" wrote in message
...
Hi all

I have a large worksheet and have set an AutoFilter on the data, to
display
the information I need, however, I now need to insert a new column
(A) and
number the entries on the filtered list as 1, 2, 3 etc.

When I enter 1 and 2, highlight them both and drag down, it doesn't
fill in
with 3, 4 etc. it gives me a list of 1's. I am presuming this is
because it
recognises the list is filtered.

Is there any way I can auto-number this filtered list?

THank you.
Louise