Which function to use????

  • Thread starter Thread starter Jane
  • Start date Start date
J

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
 
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)
 
Back
Top