| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Same dates in Sheet1 put as one date in Sheet2
In the workbook Ive two sheets.
In Sheet1 ColumnA Ill 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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Same dates in Sheet1 put as one date in Sheet2
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Same dates in Sheet1 put as one date in Sheet2
Hi
One way would be to use Advanced Filter Mark data in column A of sheet1, DataFilterAdvanced FilterClick Unique values onlyCopy toSheets!A1 -- Regards Roger Govier "inta251 via OfficeKB.com" <u30987@uwe wrote in message news:6c825d5579942@uwe... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Same dates in Sheet1 put as one date in Sheet2
Thanks for quick respond.
Formula working as I need it. Only one problem. If no new date in Sheet1 ColumnA, cells in Sheet2 ColumnA show #NUM! result Sample: Row1 Date Row2 January 08, 2007 Row3 January 09, 2007 Row4 January 10, 2007 Row5 January 11, 2007 Row6 January 12, 2007 Row7 January 14, 2007 Row8 January 15, 2007 Row9 January 16, 2007 Row10 January 17, 2007 Row11 January 19, 2007 Row12 #NUM! Row13 #NUM! Row14 #NUM! Row15 #NUM! Row16 #NUM! Row17 #NUM! Row18 #NUM! Row19 #NUM! Row20 #NUM! and so on When new date insert in Sheet1, #NUM! in Row12 will be change to that date. And so on. {=INDEX(Sheet1!$A$2:$A$21,SMALL(IF(Sheet1!$A$3:$A$ 22<Sheet1!$A$2:$A$21,ROW (Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROW()-ROW($B$2)+1))} Thank agaim. Sincerely, Igor. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200701/1 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Same dates in Sheet1 put as one date in Sheet2
If you want the formula to display blank instead of #NUM! you can use
the following variant (again array formula): =IF(ISNUMBER(SMALL(IF(Sheet1!$A$3:$A$22<Sheet1!$A $2:$A$21,ROW(Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROW()-ROW($B$2)+1)),INDEX(Sheet1!$A$2:$A$21,SMALL(IF(She et1!$A$3:$A$22<Sheet1!$A$2:$A$21,ROW(Sheet1!$A$2: $A$21)-ROW(Sheet1!$A$2)+1),ROW()-ROW($B$2)+1)),"") HTH Kostis inta251 via OfficeKB.com wrote: Thanks for quick respond. Formula working as I need it. Only one problem. If no new date in Sheet1 ColumnA, cells in Sheet2 ColumnA show #NUM! result Sample: Row1 Date Row2 January 08, 2007 Row3 January 09, 2007 Row4 January 10, 2007 Row5 January 11, 2007 Row6 January 12, 2007 Row7 January 14, 2007 Row8 January 15, 2007 Row9 January 16, 2007 Row10 January 17, 2007 Row11 January 19, 2007 Row12 #NUM! Row13 #NUM! Row14 #NUM! Row15 #NUM! Row16 #NUM! Row17 #NUM! Row18 #NUM! Row19 #NUM! Row20 #NUM! and so on When new date insert in Sheet1, #NUM! in Row12 will be change to that date. And so on. {=INDEX(Sheet1!$A$2:$A$21,SMALL(IF(Sheet1!$A$3:$A$ 22<Sheet1!$A$2:$A$21,ROW (Sheet1!$A$2:$A$21)-ROW(Sheet1!$A$2)+1),ROW()-ROW($B$2)+1))} Thank agaim. Sincerely, Igor. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200701/1 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Same dates in Sheet1 put as one date in Sheet2
With this formula cell is blank.
When put new date in Sheet1, cell in Sheet2 the same (blank) and cells in the same row start do function, because they link to that cell in ColumnA. No changes. vezerid wrote: If you want the formula to display blank instead of #NUM! you can use the following variant (again array formula): {=IF(ISNUMBER(SMALL(IF(Sheet1!$A$3:$A$22<Sheet1!$ A$2:$A$21,ROW(Sheet1!$A$2: $A$21)-ROW(Sheet1!$A$2)+1),ROW()-ROW($B$2)+1)),INDEX(Sheet1!$A$2:$A$21,SMALL (IF(Sheet1!$A$3:$A$22<Sheet1!$A$2:$A$21,ROW(Sheet 1!$A$2:$A$21)-ROW(Sheet1!$A $2)+1),ROW()-ROW($B$2)+1)),"")} -- Message posted via http://www.officekb.com |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Same dates in Sheet1 put as one date in Sheet2
Please, disregard previous message.
With few adjustments formula got back to work for me. Thanks again. Sincerely, Igor. {=IF(ISNUMBER(SMALL(IF(Sheet1!$A$3:$A$22<Sheet1!$ A$2:$A$21,ROW(Sheet1!$A$2: $A$21)-ROW(Sheet1!$A$2)+1),ROW()-ROW($B$2)+1)),INDEX(Sheet1!$A$2:$A$21,SMALL (IF(Sheet1!$A$3:$A$22<Sheet1!$A$2:$A$21,ROW(Sheet 1!$A$2:$A$21)-ROW(Sheet1!$A $2)+1),ROW()-ROW($B$2)+1)),"")} -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200701/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return Matched Numeric Values across Rows | Excel Worksheet Functions | |||
| Deleting time part of a Date, subtracting dates | Excel Discussion (Misc queries) | |||
| Traffic Light Filter On Dates | Excel Discussion (Misc queries) | |||
| merging sheet1 to sheet2 | Excel Worksheet Functions | |||
| Compare dates (one cell not in date format) | Excel Discussion (Misc queries) | |||
Linear Mode
