Non-blanks list according to date range (half done)

[D_N_L]

Hi all,

I have a need to condense a list (containing blanks) into a new list without blanks. For this Chip Pearson’s great array formula works flawlessly!:

Please Login or Register  to view this content.

However I want to apply this based on a user picked start date (cell F1) and an end date (F2). I think I need the ranges in the formula to react dynamically to what is chosen in cells F1 and F2 but I’m not sure how to go about it?

[Richard Buttrey]

Hi,

WHy not just sort the list? If necessary and if you want to get back to the original order, add a temporary helper column and fill itg with the ascending series 1,2,3 ...etc, include this in the sort, then select all the non blank range and sort back on the helper column.

[D_N_L]

Hi Richard,

That would certainly work. However I am building it within a dashboard so I'm not looking to add any more user steps. At the moment all they need to do is choose a start and end date...

[oeldere]

@D_N_L

I think the offered help with helpcolumn is very usefull.

Otherwise you have to implement the other statement in your formula.

Are you capable to do so, and re-implement the formula if is it is delete by users.

[Richard Buttrey]

Hi,

...well, you could write a simple macro to perform those steps but are you ignoring the obvious Advanced Data Filter with a criteria range that incorporates your Start and End dates and also specifies non blanks in the field in question?

[D_N_L]

Great - thanks I'll check out the ADF then I think. Thanks for the steer all.

: )