serial numbers with hidden rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want know whether it is possible to create continuous serial numbers in
rows of column A without taking in to account of the rows that are hidden.
Again when I un hide the rows the numbers should automatically change in
sequence. I got a macro for doing this from community. But will it possible
to do that through a formula?
Will some one throw light on this?
 
You can use the SUBTOTAL function to count the visible rows. For
example, assuming there will always be an entry in column B, enter the
following formula in row 2,
=SUBTOTAL(103,$B$2:B2)
Copy the formula down to the last row of data

If you're using Excel 2002 or earlier, you can use this formula instead:
=SUBTOTAL(3,$B$2:B2)
and the formula will only ignore rows that are hidden by a filter, not
those that are manually hidden.

But, don't use this technique in conjunction with the Data>Subtotals
command. If 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.
 
Back
Top