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

  • 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

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

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
 

Attachments

  • xpo loading demo.xlsm
    132.3 KB · Views: 6
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
Bosses may ask what they want. It is rarely what they need. So if you could deliver that, that would be awesome :).
 
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.
 

Attachments

  • xpo loading demo.xlsm
    131.7 KB · Views: 2
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?
View attachment 48669

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.

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

Attachments

  • xpo loading demo2.xlsm
    131.6 KB · Views: 0
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
 
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.
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.
 

Attachments

  • xpo loading demo.xlsm
    132 KB · Views: 1
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
 
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
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
 

Attachments

  • xpo loading demo.xlsm
    132 KB · Views: 2
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.
 
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.
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.:))
 
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
 

Attachments

  • xpo loading demo (3).xlsm
    133.8 KB · Views: 2
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#
 
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
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...
 
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
 

Attachments

  • xpo loading demo (4).xlsm
    133.2 KB · Views: 2
Back
Top