Which function to use????

[Jane]

I have a worksheet that has dates to track employee
absences. What I need is a way to report the dates for
which the employee was gone. How do I get the dates that
are entered for the absences into a range of cells (in the
current worksheet or a new worksheet) without Excel
copying the blank cells that may occur between absences,
automatically? I only want the range to show the date if
a value was entered in the corresponding cell. If nothing
was entered in the corresponding cell, I don't want
anything to happen. Hope this makes sense...Thank you for
any/all help

Jane

 

[Frank Kabel]

Hi Jane
if you're willing to use a formula 8and not considering using Advanced
filters9 you may try the following:
enter this formula in row 1 (e.g. in cell C1) as array formula (entered
with CTRL+SHIFT+ENTER):

=IF(ISERROR(INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100<>"",ROW($A$1:$A$100)
,1000),ROW()))),"",INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100<>"",ROW($A$1:
$A$100),1000),ROW())))
and copy down (assumption: column A stores your dates)