=ROW-1 modified coding in filters not working in Excel 2002.

[StargateFanFromWork]

From this message here:

(http://groups.google.ca/groups?hl=en&lr=&ie=UTF-
8&threadm=i4esc01gdj2n0kqqevo1rfdfpdahvc21e9%
404ax.com&rnum=9&prev=/groups%3Fq%3Dstargatefan%26hl%3Den%
26lr%3D%26ie%3DUTF-8%26group%
3Dmicrosoft.public.excel.programming%26scoring%3Dd%26selm%
3Di4esc01gdj2n0kqqevo1rfdfpdahvc21e9%25404ax.com%26rnum%
3D9)

I was giving this coding to get automatic line numbering
that would work whether filtered or unfiltered data was
being looked at:

in A2, =SUBTOTAL(2,$B$2:B2)

This has worked fine in all the E2K worksheets I've used
it on but isn't working in Excel 2002.

Any ideas on why that might be? I get a 0 in every single
cell I use the above on.

Thanks!

 

[Bernie Deitrick]

Hey, I remember you!

You are getting 0s because your list isn't numbers (or, if they look like
numbers, they are actually entered as strings). Instead, try using

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

HTH,
Bernie
MS Excel MVP

 

[StargateFan]

Hey, I remember you!

You are getting 0s because your list isn't numbers (or, if they look like
numbers, they are actually entered as strings).

Must admit that I didn't understand all the above; that's the trouble
with being a power user newbie, as I like to think of myself. <g>
Sounds good, though <g>.

Instead, try using

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

I'll give this a try when I get to work today.

It's a real handy thing to be able to get a total number of records
whether filtered or not. It makes for a good error detection tool, as
well. We'll print the main list and that will give show the total
number of records. Each printed filtered list will also give a total
number of records for that partial list. When all these lists are
added to get a total, that should obviously give us the same total as
the master list. Getting a different total will signal missing data
somewhere that we can retrieve.

Thanks, will incorporate this code first thing this morning when I get
to the office.