Conditional Formatting w/ Conditional Functions... using AND

G

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
 
D

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

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

KWGSNA said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top