Hello

I have a worksheet with a column that has an exp date. If the date is
today's date or a previous date I would like to have the date turn red. (I
think this function is in conditional formatting but im unsure of a formula)
help! and thanks

Hi
in the conditional format dialog enter a formzla
=A1<=TODAY()

--
Regards
Frank Kabel
Frankfurt, Germany


Markitos wrote:
Hello

I have a worksheet with a column that has an exp date. If the date is
today's date or a previous date I would like to have the date turn
red. (I think this function is in conditional formatting but im
unsure of a formula) help! and thanks

THANK YOU VERY MUCH. YOU JUST HELPED ME OUT SO MUCH!!!

"Frank Kabel" wrote:

Hi
in the conditional format dialog enter a formzla
=A1<=TODAY()

--
Regards
Frank Kabel
Frankfurt, Germany


Markitos wrote:
Hello

I have a worksheet with a column that has an exp date. If the date is
today's date or a previous date I would like to have the date turn
red. (I think this function is in conditional formatting but im
unsure of a formula) help! and thanks

Thanks again frank! Hey, I was wondering if their is a way to transfer the
date to another worksheet within a workbox if the exp date is expired. Also
would it be possible to make it so the their will be no spaces left blank
(condense to fill in top-bottem). Thx-Markitos

"Markitos" wrote:

Hello

I have a worksheet with a column that has an exp date. If the date is
today's date or a previous date I would like to have the date turn red. (I
think this function is in conditional formatting but im unsure of a formula)
help! and thanks

"Markitos" wrote
.. was wondering if their is a way to transfer the
date to another worksheet within a workbox
if the exp date is expired. Also would it be possible
to make it so the their will be no spaces left blank
(condense to fill in top-bottem) ..

Give this a play ..

Assume you have in Sheet1, cols A to C
data from row2 down

Date Field1 Field2
31-Oct-04 Data1 Data2
01-Nov-04 Data1 Data2
02-Nov-04 Data1 Data2
03-Nov-04 Data1 Data2
04-Nov-04 Data1 Data2
05-Nov-04 Data1 Data2
06-Nov-04 Data1 Data2
etc

Using an empty col to the right of the data, say, col E

Put in E2: =IF(A2="","",IF(A2<TODAY(),ROW(),""))
Copy down as many rows as data is expected in cols A to C

In a new Sheet2
----------------------
With the same col headers in A1:C1, viz:
Date Field1 Field2

Put in A2:

=IF(ISERROR(MATCH(SMALL(Sheet1!$E:$E,ROW(A1)),Shee t1!$E:$E,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$E:$E,ROW(A1)),Sheet1! $E:$E,0)-1,COLUMN(A1)-1))

Copy A2 across to C2, fill down by as many rows
as was done in col E of Sheet1

Format col A as dates

Sheet2 will extract only* the rows from Sheet1
where the dates in col A have since expired,
i.e. < TODAY() [as per formula in col E of Sheet1]
*and without any blank rows in-between

For the sample data in Sheet1,
it'll show** in Sheet2 as:

Date Field1 Field2
31-Oct-04 Data1 Data2
01-Nov-04 Data1 Data2

**"Today" is : 2-Nov-2004 over here
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----

THX VERY MUCH MAX!
I tried to put this into my wookbook I MANAGED TO GET THE FIRST FORMULA TO
WORK,BUT THE SECOND IS CONFUSING ME.

IN MY WOOKBOOK I HAVE 8 SHEETS TOTAL . 7 are sheets with dates and one is to
be the report sheet.

on the sheets the dates are entered on column J, and I have the formula on
column P. (THIS FORMULA IS WORKING FINE).
one the reports sheet it is formatted the same as the other sheets. I tried
to insert the formula with cell/ # changes but it wouldnt work and im
confused with the -1's in the formula.
Also Im unsure if it would be different if I have 7 sheets which I want to
pull from. Also I was trying to have the the info from cells A THROUGH O
COPIED ONTO THE REPORTS PAGE.
I KNOWN THIS QUITE A CHALLENAGE, BUT ANY HELP OR ADVICE WOULD BE HELPFUL.

THANKS-AGAIN.

"Max" wrote:

"Markitos" wrote
.. was wondering if their is a way to transfer the
date to another worksheet within a workbox
if the exp date is expired. Also would it be possible
to make it so the their will be no spaces left blank
(condense to fill in top-bottem) ..

Give this a play ..

Assume you have in Sheet1, cols A to C
data from row2 down

Date Field1 Field2
31-Oct-04 Data1 Data2
01-Nov-04 Data1 Data2
02-Nov-04 Data1 Data2
03-Nov-04 Data1 Data2
04-Nov-04 Data1 Data2
05-Nov-04 Data1 Data2
06-Nov-04 Data1 Data2
etc

Using an empty col to the right of the data, say, col E

Put in E2: =IF(A2="","",IF(A2<TODAY(),ROW(),""))
Copy down as many rows as data is expected in cols A to C

In a new Sheet2
----------------------
With the same col headers in A1:C1, viz:
Date Field1 Field2

Put in A2:

=IF(ISERROR(MATCH(SMALL(Sheet1!$E:$E,ROW(A1)),Shee t1!$E:$E,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$E:$E,ROW(A1)),Sheet1! $E:$E,0)-1,COLUMN(A1)-1))

Copy A2 across to C2, fill down by as many rows
as was done in col E of Sheet1

Format col A as dates

Sheet2 will extract only* the rows from Sheet1
where the dates in col A have since expired,
i.e. < TODAY() [as per formula in col E of Sheet1]
*and without any blank rows in-between

For the sample data in Sheet1,
it'll show** in Sheet2 as:

Date Field1 Field2
31-Oct-04 Data1 Data2
01-Nov-04 Data1 Data2

**"Today" is : 2-Nov-2004 over here
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----

Ah .. looks like the specs have expanded somewhat <g

The set-up suggested earlier assumes a *single* source Sheet1 where data
rows with expired dates are to be extracted into another Sheet2 (the # of
cols to be extracted over is not a prob)

If you could re-design your set-up to be into a single source
instead of 7 separate sheets, or consider
performing a daily manual copy paste data operation
from all 7 source sheets into a single source "Sheet1"
(just copy *all* data rows from each of the 7 source sheets and "stack" them
up block by block below each other in any order in Sheet1), then this'll
work ..

In Sheet1 (< the sheet with consolidated data)
-------------
Dates are in col J, J2 down

Put in say, P2: =IF(J2="","",IF(J2<TODAY(),ROW(),""))

Copy P2 down by a safe "max" number of rows
to cover the consolidated data expected in cols A to O,
say down to P2000

(think you got this part working earlier ..)

In Sheet2
-------------
CopyPaste the headers for cols A to O
from Sheet1 into A1:O1

Put in A2:

=IF(ISERROR(MATCH(SMALL(Sheet1!$P:$P,ROW(A1)),Shee t1!$P:$P,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$P:$P,ROW(A1)),Sheet1! $P:$P,0)-1,COLUMN(A1)-1))

Copy across to O2, then fill down to O2000

The formulas in Sheet2 will extract only rows
where the dates in col J have expired in Sheet1

The "-1" in ..COLUMN(A1)-1..
(this is the cols param in the OFFSET formula)
is just an arithmetic adjustment made to
ensure that the OFFSET formula returns the correct results
in the start cell A2, and as we copy A2 across to O2

"COLUMN(A1)-1" in A2 will return zero
as we copy A2 across to B2, it'll change to
"COLUMN(B1)-1" in B2 which returns 1, and so on

(we're using COLUMN() as the incrementer as we copy across)

In A2, the zero in the cols param means extract from col zero
i.e. col A, the same column as the OFFSET's base reference
cell: $A$1.

In B2, "1" in the cols param will mean extract from one col to the right of
col A, i.e. from col B. And so on.
--
If you'd like to have a sample file via private email,
just post a "readable" email add in response here
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----
"Markitos" wrote in message
...
THX VERY MUCH MAX!
I tried to put this into my wookbook I MANAGED TO GET THE FIRST FORMULA TO
WORK,BUT THE SECOND IS CONFUSING ME.

IN MY WOOKBOOK I HAVE 8 SHEETS TOTAL . 7 are sheets with dates and one is
to
be the report sheet.

on the sheets the dates are entered on column J, and I have the formula on
column P. (THIS FORMULA IS WORKING FINE).
one the reports sheet it is formatted the same as the other sheets. I
tried
to insert the formula with cell/ # changes but it wouldnt work and im
confused with the -1's in the formula.
Also Im unsure if it would be different if I have 7 sheets which I want to
pull from. Also I was trying to have the the info from cells A THROUGH O
COPIED ONTO THE REPORTS PAGE.
I KNOWN THIS QUITE A CHALLENAGE, BUT ANY HELP OR ADVICE WOULD BE HELPFUL.

THANKS-AGAIN

Thx Max your so good. What i ended up doing was keep all seven sheets but i
added another and condenced all seven into one, then put the formula in with
just changing the sheet.

YES I HAVE YET ANOTHER QUESTION!!!!!!!!!!!!!!!!YEP....
I WAS TRYING TO SORT THE REPORT PAGE BY DATE AND FAILED. WHAT IAM TRYING TO
ACC. IS TO GET THE SHEET TO AUTO SORT BY DATE. W/ OLDEST DATE COMPARED TO
TODAY TO BE ON TOP THEN SO ON.

THX- MUCHO!, YOUR P I M P

"Max" wrot:

Ah .. looks like the specs have expanded somewhat <g

The set-up suggested earlier assumes a *single* source Sheet1 where data
rows with expired dates are to be extracted into another Sheet2 (the # of
cols to be extracted over is not a prob)

If you could re-design your set-up to be into a single source
instead of 7 separate sheets, or consider
performing a daily manual copy paste data operation
from all 7 source sheets into a single source "Sheet1"
(just copy *all* data rows from each of the 7 source sheets and "stack" them
up block by block below each other in any order in Sheet1), then this'll
work ..

In Sheet1 (< the sheet with consolidated data)
-------------
Dates are in col J, J2 down

Put in say, P2: =IF(J2="","",IF(J2<TODAY(),ROW(),""))

Copy P2 down by a safe "max" number of rows
to cover the consolidated data expected in cols A to O,
say down to P2000

(think you got this part working earlier ..)

In Sheet2
-------------
CopyPaste the headers for cols A to O
from Sheet1 into A1:O1

Put in A2:

=IF(ISERROR(MATCH(SMALL(Sheet1!$P:$P,ROW(A1)),Shee t1!$P:$P,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$P:$P,ROW(A1)),Sheet1! $P:$P,0)-1,COLUMN(A1)-1))

Copy across to O2, then fill down to O2000

The formulas in Sheet2 will extract only rows
where the dates in col J have expired in Sheet1

The "-1" in ..COLUMN(A1)-1..
(this is the cols param in the OFFSET formula)
is just an arithmetic adjustment made to
ensure that the OFFSET formula returns the correct results
in the start cell A2, and as we copy A2 across to O2

"COLUMN(A1)-1" in A2 will return zero
as we copy A2 across to B2, it'll change to
"COLUMN(B1)-1" in B2 which returns 1, and so on

(we're using COLUMN() as the incrementer as we copy across)

In A2, the zero in the cols param means extract from col zero
i.e. col A, the same column as the OFFSET's base reference
cell: $A$1.

In B2, "1" in the cols param will mean extract from one col to the right of
col A, i.e. from col B. And so on.
--
If you'd like to have a sample file via private email,
just post a "readable" email add in response here
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----
"Markitos" wrote in message
...
THX VERY MUCH MAX!
I tried to put this into my wookbook I MANAGED TO GET THE FIRST FORMULA TO
WORK,BUT THE SECOND IS CONFUSING ME.

IN MY WOOKBOOK I HAVE 8 SHEETS TOTAL . 7 are sheets with dates and one is
to
be the report sheet.

on the sheets the dates are entered on column J, and I have the formula on
column P. (THIS FORMULA IS WORKING FINE).
one the reports sheet it is formatted the same as the other sheets. I
tried
to insert the formula with cell/ # changes but it wouldnt work and im
confused with the -1's in the formula.
Also Im unsure if it would be different if I have 7 sheets which I want to
pull from. Also I was trying to have the the info from cells A THROUGH O
COPIED ONTO THE REPORTS PAGE.
I KNOWN THIS QUITE A CHALLENAGE, BUT ANY HELP OR ADVICE WOULD BE HELPFUL.

THANKS-AGAIN