| Home |
| Search |
| Today's Posts |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transfer data relevent to date
"Max" wrote: "KandK" wrote [slightly typo corrected] : I have a worksheet that has columns of information sorted by date. What I would like to do is transfer the relevant cells of information for todays date to another worksheet which would automatically change the cells of information as the date changes. Here's a play using non-array formulas .. Assume source data is in sheet: X cols A to C, data from row2 down (Col A = dates) Date Field1 Field2 26-Apr-06 15 19 26-Apr-06 11 15 27-Apr-06 16 13 27-Apr-06 19 10 27-Apr-06 17 16 28-Apr-06 10 15 28-Apr-06 20 13 29-Apr-06 12 20 30-Apr-06 11 14 etc Let's create a defined name to evaluate "today's date" Click Insert Name Define Make the settings under Names in workbook: TDay Refers to: =TODAY() Then in another sheet: Y, With the same headers in A1:C1 : Date, Field1, Field2 Put in A2: =IF(ISERROR(SMALL($D:$D,ROW(A1))),"",INDEX(X!A:A,M ATCH(SMALL($D:$D,ROW(A1)), $D:$D,0))) Copy A2 across to C2 Put in D2: =IF(X!A2="","",IF(X!A2=TDay,ROW(),"")) (Leave D1 empty) Select A2:D2, fill down to say, D50 ? to cover the max expected extent of data in X Format col A as dates Cols A to C in Y will auto-return only those lines with dates = today's date in col A in X. All lines will appear neatly bunched at the top. For the sample data above, if "today's date" is say: 27-Apr-06, we'd get: Date Field1 Field2 27-Apr-06 16 13 27-Apr-06 19 10 27-Apr-06 17 16 (blank: "" rows below) Adapt to suit .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Thank you so much for your reply, unfortunately it is a bit over my head. I was wondering if I gave a more in depth information you may be able to clarify it for me. On my worksheet Row 2 has the dates, 1 column for each date ie GA2 has 04/03/06, GB2 has 05/01/06, GC2 has 05/02/06 etc. Under each date there rows 5-138 any of which may have data but all of which I want to transfer (even if empty). I would also like to transfer the date before and date after if possible. I hope this is not too much. Thank you once again for your reply, I am truly grateful. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| pasting data from a website changes text to date | Excel Discussion (Misc queries) | |||
| Sum data by date range | Excel Discussion (Misc queries) | |||
| Transfer data relevent to date | Excel Worksheet Functions | |||
| NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
| Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Threaded Mode
