Transfer data relevent to date

[Guest]

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.

 

[CaptainQuattro]

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

 

[Max]

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 A22, 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 ..

---

 

[Guest]

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 A22, 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 ..

 

[Guest]

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

---

 

[Guest]

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

---

 

[Guest]

viz..:
Col A returns the data for yesterday
Col B returns the data for today (current date)
Col C returns the data for tomorrow

That works perfectly. Thank you so much for the help, I really do appreciate it.

 

[Guest]

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

---