Greetings to all excel-lers.
I think this is the best place for me to solve my dillema in Excel.
I run a logistics company and we use excel to make our Daily Reports as to truck locations and inbound and outbound. This report is the soul of the company because customers depend on it (both to and fro), the workshop depends on it to prepare of upcoming repairs, and management to handle invoicing.
Up until recently, data input was simple as the excel sheet was rather simple but we want to to really take on the power of excel and produce a better report.
The dilemma;
Part 1;
I have used the conditional formatting option to highlight trucks in the fleet that are inbound, outbound, in workshop, or not booked, that was easy.
I have used the IF function to annotate open and closed trips (ie trips in transit and trips where the truck has returned and offloaded and can be rebooked).
I want to now base these 4 options based on data inputs of:
a) No Loading Date Input - Means trip is 'Not Booked, trip Open'
b) Loading Date Input - Means trip is now 'Outbound, trip Open'
c) Offloading Date Input - Means trip is now 'Inbound, trip Open'
d) Return Date Input - Means trip is now 'In workshop, trip Closed'
I want it so that when I put in option d), a new row is created at bottom of sheet which reads; 'Not Booked, trip Open' with the same exact details of truck (reg no, driver, passport, cell, etc) as the existing trip is closed. I just can't seem to do this without erroneously putting data in or manually doing it.
Part 2;
I want to also be able to depending on route, block some cells from data input.
Example; sometimes, truck goes acrros border A and sometimes accross border A + B.
I know this from the start, so when i put in the route, I want it to block out data entry in border B column.
Part 3;
My excel sheet is quite large, there are 75 rows (one for each open trip/truck) and 20 columns (one for each date at point of interest on route (loading, checkpoint, border A, Border B, offloading, reloading, Border B, Border A, offloading, etc).
To maintain integrity in Data, I want to lock all cells and also be able to use a form to input data so my staff only see one truck at a time instead of 75 trucks at a go.
My excel sheet is a table so I can quickly sort out data BTW.
Please let me know if you can help, or send me an email on alydewji@gmail.com and I can send you the excel sheet so you can see what I want and how you can help me.
Best regards,
Aly
I think this is the best place for me to solve my dillema in Excel.
I run a logistics company and we use excel to make our Daily Reports as to truck locations and inbound and outbound. This report is the soul of the company because customers depend on it (both to and fro), the workshop depends on it to prepare of upcoming repairs, and management to handle invoicing.
Up until recently, data input was simple as the excel sheet was rather simple but we want to to really take on the power of excel and produce a better report.
The dilemma;
Part 1;
I have used the conditional formatting option to highlight trucks in the fleet that are inbound, outbound, in workshop, or not booked, that was easy.
I have used the IF function to annotate open and closed trips (ie trips in transit and trips where the truck has returned and offloaded and can be rebooked).
I want to now base these 4 options based on data inputs of:
a) No Loading Date Input - Means trip is 'Not Booked, trip Open'
b) Loading Date Input - Means trip is now 'Outbound, trip Open'
c) Offloading Date Input - Means trip is now 'Inbound, trip Open'
d) Return Date Input - Means trip is now 'In workshop, trip Closed'
I want it so that when I put in option d), a new row is created at bottom of sheet which reads; 'Not Booked, trip Open' with the same exact details of truck (reg no, driver, passport, cell, etc) as the existing trip is closed. I just can't seem to do this without erroneously putting data in or manually doing it.
Part 2;
I want to also be able to depending on route, block some cells from data input.
Example; sometimes, truck goes acrros border A and sometimes accross border A + B.
I know this from the start, so when i put in the route, I want it to block out data entry in border B column.
Part 3;
My excel sheet is quite large, there are 75 rows (one for each open trip/truck) and 20 columns (one for each date at point of interest on route (loading, checkpoint, border A, Border B, offloading, reloading, Border B, Border A, offloading, etc).
To maintain integrity in Data, I want to lock all cells and also be able to use a form to input data so my staff only see one truck at a time instead of 75 trucks at a go.
My excel sheet is a table so I can quickly sort out data BTW.
Please let me know if you can help, or send me an email on alydewji@gmail.com and I can send you the excel sheet so you can see what I want and how you can help me.
Best regards,
Aly
