Transfer data relevent to date

I have a workatsheet that has columns of inormation sorted by date. What I
would like to do is transfer the relevent cells of information for todays
date to another worksheet which would automatically change the cells of
information as the date changes. Can anyone please help.

Assuming your data is sorted in ascending order by date on Sheet 1, the following example will do what you want.

On Sheet1, let's say you have the following data, startin in cell A1:

Date. . . Field1. . . Field2. . . Field3
24-Apr. . .o. . . . . .oo. . . . . .ooo
25-Apr. . .p. . . . . .pp. . . . . .ppp
25-Apr. . .q. . . . . .qq. . . . . .qqq
25-Apr. . .r. . . . . .rr. . . . . .rrr
25-Apr. . .s. . . . . .ss. . . . . .sss
25-Apr. . .t. . . . . .tt. . . . . .ttt
26-Apr. . .u. . . . . .uu. . . . . .uuu
26-Apr. . .v. . . . . .vv. . . . . .vvv
26-Apr. . .w. . . . . .ww. . . . . .www
26-Apr. . .x. . . . . .xx. . . . . .xxx
26-Apr. . .y. . . . . .yy. . . . . .yyy
26-Apr. . .z. . . . . .zz. . . . . .zzz
26-Apr. . .zz. . . . . .zzzz. . . . . .zzzzzz
26-Apr. . .zzz. . . . . .zzzzzz. . . . . .zzzzzzzzz

Make sure the last few entries in the example data contain the current date.

On Sheet2

in cell J1 enter the formula =TODAY()
in cell K1 enter the formula =MATCH(J1,Sheet1!$A:$A,0)-1
in cell K2 enter the formula =K1+1

In cells A1, B1, C1 and D1 enter the headings Date; Field1; Field2; Field3
In cells A2, B2, C2 and D2 enter the formulas

=OFFSET(Sheet1!$A$1,Sheet2!$K1,0);
=OFFSET(Sheet1!$A$1,$K1,1)
=OFFSET(Sheet1!$A$1,$K1,2)
=OFFSET(Sheet1!$A$1,$K1,3)

Now copy the formulas in cells A2,B2,C2, D2 and K2 down as many rows as you need.

The cells on Sheet2 that refer to blank rows on sheet1 will contain zero's. To make these display as blank cells:

Tools> Options> View. . . and uncheck "Zero values."

"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,MATCH(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
---

"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,MATCH(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.

"KandK" wrote:
> ..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.

Aha, so that's how your set-up looks like ..

Here's one play to try ..

A sample construct is available at:
http://www.savefile.com/files/1030392
AutoDisplay Data (Yday, Today, Tmr) in new sheet

Assume source data in sheet: X,
dates in GA2:IV2, data running down in cols below dates

In another sheet: Y,

Put in B1: =TODAY()

In A2:
=IF(ISNA(MATCH($B$1-1,X!$GA$2:$IV$2,0)-1),"",OFFSET(X!$GA$2:$GA$140,,MATCH($B$1-1,X!$GA$2:$IV$2,0)-1))

In B2:
=IF(ISNA(MATCH($B$1,X!$GA$2:$IV$2,0)-1),"",OFFSET(X!$GA$2:$GA$140,,MATCH($B$1,X!$GA$2:$IV$2,0)-1))

In C2:
=IF(ISNA(MATCH($B$1+1,X!$GA$2:$IV$2,0)-1),"",OFFSET(X!$GA$2:$GA$140,,MATCH($B$1+1,X!$GA$2:$IV$2,0)-1))

Select A2:C2, fill down to say, C140
(to cover the expected extent)

A2:C140 will return the required results from X

To suppress the display of extraneous zeros in the sheet, click:
Tools > Options > View tab > Uncheck "Zero values" > OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> A2:C140 will return the required results from X
viz..:
Col A returns the data for yesterday
Col B returns the data for today (current date)
Col C returns the data for tomorrow
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Max" wrote:

> > A2:C140 will return the required results from X
> viz..:
> Col A returns the data for yesterday
> Col B returns the data for today (current date)
> Col C returns the data for tomorrow
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> That works perfectly. Thank you so much for the help, I really do appreciate it.

"KandK" wrote:
> That works perfectly.
> Thank you so much for the help, I really do appreciate it.

You're welcome ! Glad to hear that.
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---