Results 1 to 8 of 8

Thread: IF AND Statement with ISBLANK and conditional

  1. #1

    Red face IF AND Statement with ISBLANK and conditional



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 RELEASE CUSTOMS RELEASE SHIPPING COMMENTS PO START SHIP PO CANCEL DATE
    HVCD1212123 5/19/2013

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    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 ).
    Attached Files Attached Files

  3. #3

    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.

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Hi
    Sorry - I messed up trying to change the sheet from .xlsm to .xlsx. This new attachment should be OK.
    Attached Files Attached Files

  5. #5

    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.
    JULY ORDERS.xlsx

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    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

  7. #7

    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 JULY ORDERS UPDATED.xlsx

  8. #8
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Great - Im glad you resolved it.

    Herc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •