1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Loading departure sheet

Discussion in 'Ask an Excel Question' started by mike leach, Jan 7, 2018.

  1. mike leach

    mike leach Member

    Messages:
    31
    Hi guys designed a loading/departure sheet working OK. Just need a formula best for a conditional formatting for the following. Basically I need for early = green, so before the departure time. Then this is the bit I am stuck with amber = 15 minutes Lee way after the departure time. Then red for after that time. Any help please. So the format of the table is departure time, live departure time and time difference. So I want and need the all three cells to change colour depending on the the time, like stated before. In the time deference column I have traffic lights which work really. And on the other two columns I want the tick for pass eg early and on time amber ⚠ for that 15 mins and red circle cross for late. Thanks Mike.
  2. GraH - Guido

    GraH - Guido Active Member

    Messages:
    612
    Hi mike leach, can you upload a sample file, with say 3 records or so.
    And please add the CF you have tried. Should not be to difficult to make it work then.
  3. mike leach

    mike leach Member

    Messages:
    31
    Also if the tab buttons are different fonts and sizes, if you know how to change them that would be so cool as my boss wants it functional and looking professional too. Cheers Mike

    Attached Files:

  4. GraH - Guido

    GraH - Guido Active Member

    Messages:
    612
    Bosses may ask what they want. It is rarely what they need. So if you could deliver that, that would be awesome :).
  5. mike leach

    mike leach Member

    Messages:
    31
    Hi what do think of it any good like I said just a little beginner/ self learning.!! thanks for any help....
  6. GraH - Guido

    GraH - Guido Active Member

    Messages:
    612
    Hi Mike,

    Why don't you use the table with the real headers instead of the standard headers?
    Besides that, this is what you are after?
    upload_2018-1-7_15-17-3.png

    I did this with a combination of custom format (look in the cell format); a formula (I replaced yours) and finally applied conditional formatting.

    Cheers
    G.

    Attached Files:

  7. GraH - Guido

    GraH - Guido Active Member

    Messages:
    612
    not sure what you mean by the "tab buttons are different fonts and sizes".
  8. mike leach

    mike leach Member

    Messages:
    31
    nearly the red cross is only going on the one after the 15min lee way. amber is the 15 min after on time and green for on time and early. what do you mean by real headers what is that. Also how do you lock so only people can enter the date and input data.
  9. mike leach

    mike leach Member

    Messages:
    31
    hi if you look at this example the colours red green and amber that is what I have also been trying to do in conditional formatting but can't get the right formula ...

    Attached Files:

  10. mike leach

    mike leach Member

    Messages:
    31
    Sorry trying to do thousand one jobs a once, cleaning etc. Yeah the tabs/ buttons for the reset and input data etc. Want different colours etc but not different sizes or fonts
  11. GraH - Guido

    GraH - Guido Active Member

    Messages:
    612
    Then you need to update my formula in the table. I've done it.

    Headers: your second row inside the table are your headers. If you convert a range to a table, check the option "my table has headers". So they will be in the header row, where you have column 1; column 2; etc... I've updated the first table in your file.

    To lock input, you need to protect your worksheet, and only unlock the cells you want people to input. I might be wrong, but I think I have seen a tutorial of this in the blog of Chandoo.
    - first in cell format -> Protection -> check out locked for the cells that are allowed to be filled in
    - On the ribbon choose Review and protect sheet (enter a password)
    basically that's it. But it is not really secure. I mean, it can be bypassed.

    If you want to make it more professional, you may want to tune down on the used colours, the heavy borders and so. Will get you a much cleaner look.

    Attached Files:

  12. mike leach

    mike leach Member

    Messages:
    31
    2018-01-07.png 2018-01-07 (1).png 2018-01-07.png

    the formula I put in for the traffic lights was that above so that I had early green on time green 15 lee way amber and red after the fifteen minutes. the below is a image of what I want the colours to be when input live times red late amber 15 window and green on time and early. If you like me I can't relax until I have it sorted out taking days on this. thank you for your help though much appreciated ;)
    2018-01-07 (1).png
  13. GraH - Guido

    GraH - Guido Active Member

    Messages:
    612
    on the deliver tab select Design Mode
    upload_2018-1-7_15-56-31.png
    Then basically like you do this in PowerPoint use the Drawing Tools to Size and Align.
    upload_2018-1-7_15-57-26.png
    The font can be updated with the properties of the buttons (also in design view).
    upload_2018-1-7_16-0-16.png

    Attached Files:

  14. mike leach

    mike leach Member

    Messages:
    31
    that works great. How do you send the same formulas to the xpo times on the xpo departure time. cut and paste or different way? ok will do with the boarders. you can change it if you want to show me how you would have it example. That is the only way you learn. See what I mean with the image above with the buttons such as see data want to have them in line same font and just better colour scheme.
  15. mike leach

    mike leach Member

    Messages:
    31
    you clever person cheers will do!!!
  16. GraH - Guido

    GraH - Guido Active Member

    Messages:
    612
    Copy the formula inside the cell, all after the "=" sign. Press escape, to leave the cell (otherwise you remain in the edit/point mode).
    Then enter the cell where you want to paste the formula: type "=" and paste the formula. You need to do this like this because I have used table references (formula refers to table headers and not cell addresses). These are relative references by default. I could make them absolute, but that makes formulas long. E.g. [[@[Live loading times]:[Live loading times]] would be a absolute reference and if you drag this to the right, it would keep that reference.

    Colour wise: leave them out, use a table scheme. Sorry, but I hope you understand I cannot do all (I do have a life outside this forum.:))
  17. mike leach

    mike leach Member

    Messages:
    31
    ok will give it ago cheers :cool:
  18. mike leach

    mike leach Member

    Messages:
    31
    I THINK THIS LOOKS BETTER SEE WHAT YOU THINK THE ONLY THING I CANT DO IS TO HAVE THE SAME ON THE XPO SIDE AS THE WOLSELEY SIDE.

    CHEERS MIKE

    Attached Files:

  19. mike leach

    mike leach Member

    Messages:
    31
    AS I HAVE TONED IT DOWN WOULD IT BE POSSIBLE TO SHOW ME HOW TO DO THE GREEN AMBER AND RED COLOUR IN CONDITIONAL FORMATING AS IT WOULD BE EASIER TO SEE AND STAND OUT.

    CHEERS#
  20. GraH - Guido

    GraH - Guido Active Member

    Messages:
    612
    you know capitals are read as SHOUTING? Be careful with that :p
    Probably your caps lock is on.
    I won't have time any more today. Later...
  21. mike leach

    mike leach Member

    Messages:
    31
    sorry I was on excel no I didn't realise that!!!
  22. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,668
    Another option,

    1] In D10, copied down :

    =IF([@[Live loading times]]="","",([@[Live loading times]]-[@[Loading Times]])*24*60)

    2] Rule of CF as in :
    upload_2018-1-8_2-41-20.png


    Regards
    Bosco

    Attached Files:

  23. GraH - Guido

    GraH - Guido Active Member

    Messages:
    612
    Shorter, thus better!
  24. mike leach

    mike leach Member

    Messages:
    31
    if I download the file can you do it? as tried not worked!!
  25. mike leach

    mike leach Member

    Messages:
    31
    Hi Guys getting there but still can't move the formula's across to the xpo departure times, plus "red circle and cross" is only for 15 minutes over the departure time. amber is the time in between the minutes 15, and the green tick is for on time and less time than departure time. As you can see there is a colour red in top time that is what I need for the late only, after the 15 mins as it is really clear for the management to see what is early and late the screen shot is not a working example just an example what it should be like. But with your help so far nearly there. thanks... 2018-01-08 (1).png 2018-01-08.png

    Attached Files:

Share This Page