Conditional Formatting w/ Conditional Functions... using AND

[Guest]

Im looking for help with the following spreadsheet im trying to create

1) Im creating a spreadsheet for our plating department with 6 fields (Date sent, Purchase Order Number, Invoices within Purchase Order, Finish Code (color of plating), Plating Company Sent To, and Recieved Date).

For example

A B C D E
Date Sent PO # Invoices Finish Company Date Recieve


I want to use conditional formatting to have a row highlighted if these two cases are true -- the Date Sent is equal to or less than 14 days from todays date (has been at plating for 14 days or more) and the Date Recieved field must be blank for that particular row (we haven't recieved it back yet).

(Ideally, id like to be able to use yellow highlight for 2-4 weeks out and not recieved, and red for 4-6 weeks out and not recieved

And separately from the conditional formatting..
I also want to be able to have a list of each invoice in each purchase order (every purchase order contains many customer invoices) AND be be able use the 'List' feature so that I can list by Invoice number also

Thank you for any hel

 

[Dan Frederick]

Could you use something like: =AND($D11<TODAY()-
14,$E11="")?

You'll have to play with it a bit to make it work, but
that's the essence. With the proper use of the $, you
should be able to format every cell in the range exactly
the same.

Dan

-----Original Message-----
Im looking for help with the following spreadsheet im trying to create:

1) Im creating a spreadsheet for our plating department

with 6 fields (Date sent, Purchase Order Number, Invoices
within Purchase Order, Finish Code (color of plating),
Plating Company Sent To, and Recieved Date).

For example:

A B

C D
E F

Date Sent PO # Invoices

Finish Company Date Recieved

I want to use conditional formatting to have a row

highlighted if these two cases are true -- the Date Sent
is equal to or less than 14 days from todays date (has
been at plating for 14 days or more) and the Date Recieved
field must be blank for that particular row (we haven't
recieved it back yet).

(Ideally, id like to be able to use yellow highlight for

2-4 weeks out and not recieved, and red for 4-6 weeks out
and not recieved)

And separately from the conditional formatting...
I also want to be able to have a list of each invoice in

each purchase order (every purchase order contains many
customer invoices) AND be be able use the 'List' feature
so that I can list by Invoice number also.

 

[Frank Kabel]

Hi
for the conditional formating issue try the following:
- select the range of your data(assumption: this starts in row 2,
column A)
- goto the conditional format dialog and enter the formula
=AND($A2<=TODAY()-14,$A2>TODAY()-28,$F2="")
- choose your format

- second formula:
=AND($A2<=TODAY()-28,$F2="")

I didn't understand your last question though

--
Regards
Frank Kabel
Frankfurt, Germany

Im looking for help with the following spreadsheet im trying to create:

1) Im creating a spreadsheet for our plating department with 6 fields

(Date sent, Purchase Order Number, Invoices within Purchase Order,
Finish Code (color of plating), Plating Company Sent To, and Recieved
Date).

For example:

A B C D E F
Date Sent PO # Invoices Finish Company Date Recieved



I want to use conditional formatting to have a row highlighted if

these two cases are true -- the Date Sent is equal to or less than 14
days from todays date (has been at plating for 14 days or more) and the
Date Recieved field must be blank for that particular row (we haven't
recieved it back yet).

(Ideally, id like to be able to use yellow highlight for 2-4 weeks

out and not recieved, and red for 4-6 weeks out and not recieved)

And separately from the conditional formatting...
I also want to be able to have a list of each invoice in each

purchase order (every purchase order contains many customer invoices)
AND be be able use the 'List' feature so that I can list by Invoice
number also.