IF AND Statement with ISBLANK and conditional

emartz

New member
Joined
May 17, 2013
Messages
4
Reaction score
0
Points
0
Hi-

I'd like to have cells turn RED via a conditional based on the following:

If the column called CONTAINER # does not have a value entered (EX: HCU1212123) 2 days prior to the column PO CANCEL DATE (ex: 05/20/2013) then the cells turn RED based on a conditional to denote the shipment as late. Basically if a container # is not entered in by 05/18 then cells would turn red and we can consider the shipment as late.

I have tried an IF AND statement with a ISBLANK but it doesn't seem to be formulating the correct end value. I am thinking the data also needs to reference either TODAY's date or the date the CONATINER # information was entered in the spreadsheet.

any help would be much appreciated.

Spreadsheet set up:

CONTAINER #FREIGHT RELEASECUSTOMS RELEASESHIPPING COMMENTSPO START SHIPPO CANCEL DATE
HVCD1212123 5/19/2013
 
Hello Emartz
Have a look at the attachment. Ive added a couple of columns to include the date when the Container # is input, and the number of days between this and the cancel date. Todays date goes in Col F automatically when a value is entered in Col G. The rows thatgo across Cols F to L are filled with red if there are less than 2 days in Col N. This is done by applying conditional formatting which I haven't used a lot, and I found an absolute pig to do what I wanted :eek:).
 

Attachments

  • Containers.xlsx
    13.6 KB · Views: 24
Attachment does not open

Hi There-


Can you re-send the attachment in a different format by any chance?
When I tried opening the file it gave me the following error message "file format or file extension does not match". I am un-able to see the spreadsheet.

Thanks.
 
Hi
Sorry - I messed up trying to change the sheet from .xlsm to .xlsx. This new attachment should be OK.
 

Attachments

  • Containers.xlsx
    13.2 KB · Views: 14
Additional Questions

This looks awesome, however I realized I need to be more specific with how exactly the data is being pulled.

The container # is pulled into the tab (called JULY ORDERS) by a vlookup which finds the information based on a PO # on a separate tab called "ORDER TRACKER".
Both the JULY ORDERS and ORDER TRACKER tabs have a column for the PO # (hence the vlookup)

The formula provided works only if I input the IF formula in the PO TRACKER tab and insert a Vlookup to extract the DATE CONTAINER # ENTERED into the JULY ORDERS tab. This works perfectly but this is where I encounter a few problems:
-The ORDER TRACKER tab is updated every other day by a different group which is then sent to me and I then have to copy and paste over the old ORDER TRACKER information, I’m afraid by copying and pasting it will skew the DATE CONTAINER # ENTERED column (IF formula) to the date I am updating the data and not “freeze” the actual date entered
-The group entering the information and sending it to me cannot add a column with the IF formula

Basically, I would want the IF formula (DATE CONTAINER # ENTERED) to live in the JULY ORDERS tab but when I input the IF formula in this tab it generates a date even if the container # is not entered because the formula picks up any data reflected in the cell (either a 0, Cancelled or #NA) and inputs today’s date

The July ORDER tab is where the information is being reviewed and where the conditional lives.
Is there a way to have the formula live in the JULY ORDERS tab without it picking up the other type of data and also not skewing the date since the ORDER TRACKER tab is updated a few times a week?

Hope that makes sense, I attached an example.
View attachment JULY ORDERS.xlsx
 
Hi There

If you are going to have the date formula in the ORDERS sheet, then the dates will reflect when this sheet is updated with the CONTAINER rather than the PO TRACKER. At present, the date formula only has two options (blank or not blank). You can remove the formula if you like, and enter the date manually. If you want to keep it, then you can extend it to exclude anything that isn't a container number.
This does mean that there must be a way to recognise one e.g. =IF(LEN(B8=12..... instead of B8<>"". The other point I'd mention is that the date function uses iteration so that the spreadsheet won't identify a recursive formula as it normally does. If you decide to go with manual dating then you can reset this in Options/Formula.

HTH

Hercules
 
got it!

Hi--got it, I inserted an IF statement in the vlookup that pulls the container # from the other tab that way it returns a blank cell if there is no value in the second tab instead of a 0.
That way the IF statement for the date container # entered doesn't pull the information unless there is a container # on the other tab and inputs the proper date once the info is entered.
see attached!

thanks for all your help View attachment JULY ORDERS UPDATED.xlsx
 
Back
Top