Assuming data in Sheet1 is in A2:A21. Assuming your formulas start at
B2 of active sheet. This formula assumes that the dates in Sheet1 are
in increasing order.

The formula is an *array* formula, which means you must commit it with
Ctrl+Shift+Enter.

=INDEX(Sheet1!$A$2:$A$21,SMALL(IF(Sheet1!$A$3:$A$2 2<Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROW()-ROW($B$2)+1))

HTH
Kostis Vezerides

inta251 via OfficeKB.com wrote:
In the workbook I've two sheets.
In Sheet1 ColumnA I'll put dates.
Same date can be repeated to infinity.
Row1 will be 'Freeze Panes'

Sample:
ColumnA
Row1 Date
Row2 01/01/2007
Row3 01/01/2007..................as one day in Sheet2
Row4 01/02/2007
Row5 01/02/2007
Row6 01/02/2007
Row7 01/02/2007
Row8 01/02/2007..................as one day in Sheet2
Row9 01/03/2007
Row10 01/03/2007
Row11 01/03/2007..................as one day in Sheet2
Row12 01/04/2007..................as one day in Sheet2
Row13 01/05/2007..................as one day in Sheet2
and so on

In Sheet2 ColumnA I need formula or Macro or VB
take new date inserted in Sheet1 and put same date
to Sheet2 ColumnA to the next blank cell.
Row1 will be 'Freeze Panes'

In my case I need same dates in Sheet1 put as one date
in Sheet2 ColumnA to the next available blank cell.

Sample:
ColumnA
Row1 Date
Row2 01/01/2007
Row3 01/02/2007
Row4 01/03/2007
Row5 01/04/2007
Row6 01/05/2007
and so on

Thanks in advance.
Sincerely, Igor.

--
Message posted via http://www.officekb.com