ExcelBanter - View Single Post

"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.