Making a dynamic list.

[technician12]

Hello.

I've been tinkering with making a dynamic list, but i might be going the wrong way about it.
If you see the sample workbook, Theres a problem in C14.D14, even though the cell in "blank" its not a true blank cell, therefore its still counted and numbered, resulting in a "hole" in the generated list ( Based on J10:J17 ), that's being used in F4.

Any way to make the "blank" cell not counted and therefore the list being without these holes ?

Sample.xlsx

[p45cal]

If you've used SUBTOTAL because of the way it behaves with filtered/hidden rows this won't work:
In cell C9 of your sheet:
=IF(D9<>"",1,"")

In cell C10:
=IF(D10<>"",MAX(C$9:C9)+1,"")
copy down to C21.

[technician12]

Now i feel like a complete idiot, i got so set on using SUBTOTAL, that i dident even think about much simpler ways of doing it.
Thanks alot!, it works like a charm.

For future projects, if anyone know a way to make a cell "true" blank, while still holding a formula ( either with VBA or without ), i'd still appreciate it ( also because i might learn something ).

[Bob Phillips]

If it has a formula, it is not blank. If you want to exclude such 'blanks', try something like

=SUMPRODUCT(--LEN(A1:A10)>0)