PDA

View Full Version : Making a dynamic list.



technician12
10-01-2020, 10:42 PM
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 ?

27255

p45cal
10-02-2020, 04:16 PM
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
10-04-2020, 10:26 PM
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 :D).

Bob Phillips
10-05-2020, 05:04 AM
If it has a formula, it is not blank. If you want to exclude such 'blanks', try something like

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