Laura Michelle
New Member
Hello -
I inherited a spreadsheet that was very basic in nature and I am in the process of updating the functionality of it. Basically, the spreadsheet is used to track purchase orders/change notices that are sent to our suppliers and the date that the supplier responds that they received the file (not they that accept it, but only received it). This is based on a corrective action that was put into place after an adverse finding during an audit approximately 5 years or so ago.
The data within the spreadsheet is also functioning to illustrate both buyer and supplier metrics based on the status.
What I am trying to accomplish is to populate the status column utilizing conditional formatting - but the conditions are complex. I need the status to return a TEXT status as well as format the cell in specific colors (based on the status returned).
Please refer to my uploaded sample spreadsheet.
The spreadsheet contains Date Due and Date Affirmed columns. I need the following (praying it is possible):
ON TIME if Date Affirmed is less than or equal to Date Due (cell formatted Green with white bold text)
LATE if Date Affirmed is greater than Date Due (cell formatted Yellow with black bold text)
PENDING if Date Affirmed is blank AND Due Date is less than or equal to Today (cell formatted Blue with white bold text)
NO RESPONSE if Date Affirmed is blank AND Due Date is greater than Today (cell formatted Red with white bold text)
NOT REQ'D if Date Affirmed equals NR (cell formatted gray with black bold text)
Any and all help would be Greatly appreciated.
Laura
I inherited a spreadsheet that was very basic in nature and I am in the process of updating the functionality of it. Basically, the spreadsheet is used to track purchase orders/change notices that are sent to our suppliers and the date that the supplier responds that they received the file (not they that accept it, but only received it). This is based on a corrective action that was put into place after an adverse finding during an audit approximately 5 years or so ago.
The data within the spreadsheet is also functioning to illustrate both buyer and supplier metrics based on the status.
What I am trying to accomplish is to populate the status column utilizing conditional formatting - but the conditions are complex. I need the status to return a TEXT status as well as format the cell in specific colors (based on the status returned).
Please refer to my uploaded sample spreadsheet.
The spreadsheet contains Date Due and Date Affirmed columns. I need the following (praying it is possible):
ON TIME if Date Affirmed is less than or equal to Date Due (cell formatted Green with white bold text)
LATE if Date Affirmed is greater than Date Due (cell formatted Yellow with black bold text)
PENDING if Date Affirmed is blank AND Due Date is less than or equal to Today (cell formatted Blue with white bold text)
NO RESPONSE if Date Affirmed is blank AND Due Date is greater than Today (cell formatted Red with white bold text)
NOT REQ'D if Date Affirmed equals NR (cell formatted gray with black bold text)
Any and all help would be Greatly appreciated.
Laura
