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

Enter data in different way ,easiest way ..

crenson313

crenson313

New Member
Good day, Chandoo's Ninjas.

I have a problem in my excel, I applied what I learned to your site and here's my example workbook, that I called "Drivers Delivery Report" in this workbook you will see my main sheet, it has the chart button that links to other sheets, I also applied the dashboard thingy. so here's my problem, Every time I want to put the data in my workbook it feels like there somethings wrong cause I always enter the data manually not automatically, then I bumped to what they called "User form" and I find it very useful however I don't know much how it works, the codes and everything that will make my workbook easy to use.

Now I want to put a userform in my workbook, I know VB but not too much, I also watched the step by step on how to apply the form but it gets me every time when I see the codes that will be needed to make things work out.


PS: For example, in the main sheet there are different destinations then iIclick the chart button " manila I " and it will automatically put me in the sheet named " Manila I " where I can put all my data then i started to type the data in " Vehicle type", "Departure", "Arrival", "L", "P", "DEMS","DFA","N" in Delivery loaded as well as the Delivery Returned, the total is at ease as it automatically sums up all the data...

The numbering on the left side is the number (1-31) of One month of a calender with corresponding what day ( " monday", "tues', ..etc.).

In my mind if i have the userform I can easily put the data like ,,..
>* Pops up Userform* > Enter the Destination name ( Ex. Manila I ) >
Enter Date numbering (Ex. I put number One(1) in the box and it was the same as "October 1,2015) >Enter Vehicle Type > Enter Departure > Enter Arrival > Enter the value in "L", "P","DEMS","DFA", and " N" in the loaded ,, > Enter the value in "L", "P","DEMS","DFA", and " N" in returned.

I'm eager to learn these things especially ofcourse by Chandoo's but I want to know what are the thoughts, ideas of my Chandoo ninja's to make it even easier, can u help me out?

THank you Chandoo

-crenson313
 

Attachments

  • DeliveryReport_Format_2016.xlsx
    689.7 KB · Views: 23
Hello,@vletm

I don't know what you really mean, but for the change, Is it possible to have a user form while maintaining the workbook the same? coz I don't want to presssure all Chandoo's , Just applying the form is ok but if you really want to make it even better, why not? I'm gladly be happy with that :3
 
@crenson313 - better? anyway different ...
Okay, I send a sample file. This is not ready at all!
This is much lighter, no formulas, which makes heavy to use.
So far...
'Form'-sheet would be the form, that You asked.
'Loaded'-sheet would be for 'not ready reports' as well
'Eturned'-sheet would be for 'ready reports'.
There are still missing some of Your sheets, but coming later.
'Form';
1) [Make New Report] click that text and follow instructions.
( Click 'Pasay I'-text to select DESTINATION.)
You'll get selection of all destinations. Click any of destination (or gray if do not make change). Fill needed cells ...
Q) Are 'VECHILE TYPEs' based with DESTINATIONS or all VECHILE TYPEs can use everywhere? Is somewhere a list that can 'select'?
Saving still missing; it will move to 'LOADED' or 'ETURNED' (not ready/ready)
2) [LOADED] click that text and select row ... (not ready this part)
but information will move to 'Form'. You fill the rest cells and 'SAVE' (not ready). Report will move to 'ETURNED'.
>> COMING LATER >> Later You can get 'whatever' from 'ETURNED'-sheet , like 'Report' & 'Dashboard'... I just have to figure out Your needs.
I need more information, how do You uses this (or Yours)?
>>> Ideas ... Questions? <<<
 

Attachments

  • EMS.xlsm
    62.5 KB · Views: 10
Good day, Sir @vletm


I use this workbook as my delivery report for different areas,
it also shows the total volume or total delivered products in different sections
(ex."L", "P", "DEMS","DFA",& "N")

: Scenario :
We actually have MiniVan and a driver that delivers the product,
before the driver goes to his destination, The dispatcher will give the assigned vehicle to the driver,
and that paper is called " Trip ticket ", this ticket shows the details
of :
Vehicle type: <-- the dispatcher will fill up this
Departure: <-- the driver will fill up this
Arrival: <-- the driver or courier will fill up this as well as loaded and returned.

Loaded Returned:
L: L:
P: P:
DEMS: DEMS:
DFA: DFA:
N: N:



Loaded means successfully delivered to the consignee or the buyer
while Eturned or supposedly "Returned", means the remaining delivered products
has not been received by any buyer or the buyer address cannot be found etc..
After the delivery the courier or the driver will give the Trip ticket to me and I will
be the one who encode the details of a trip ticket in this workbook.

When a month is Finish, It's time to pass the report.


Q) Are 'VECHILE TYPEs' based on DESTINATIONS or all VECHILE TYPEs can use everywhere? Is somewhere a list that can 'select'?

as far as I know, yes the vehicles are based base on destinations but sometimes there different one's due to needed maintenance etc.
I have the list of vehicles per destinations but it's many, about 300 lists of vehicles, some of them are damaged and need for repairs.
while few are in need for recycling or bidding.

Q ) How do you do all that? the form you applied and the list? it's beautiful in the eyes.
Q) Can I input the list of the vehicles instead?How?


Attached below a complete report for the month of Dec 2015.
you will notice some of the data are blank because they don't have any dispatch during that day. I was planning to make a dashboard in this, for me to know the days of no dispatch.
the Report tab is for 1 month in a calendar (1-31) I usually print this as requested by my boss and planning to have a dashboard in that too.

Hoping for the outcome

Respectfully yours -

crenson313
Chandoo's Disciple
 
Last edited:
@crenson313
Small steps ...
'Form' has few working functions ..
> It's possible to 'make new report' (not ready)
> prev is possible to save to 'loaded' and 'reload' back to 'form'
>> vehicle types: You can select 'destination's vehicle types' if those are not in 'duty'. There is also 'vehicle'-sheet. There should be all You vehicles. I just collect some. You can see white/free, green/duty or pink/away (broken or so). Also if not free, there will be comment. Maybe someway gotta be possible to select vehicle from this sheet too.
> Soon: From 'From' should finish and save to 'eturned'.
>> Reports and so on. I try to move You December-data to this too.
>>> Ideas ... Hopes ... Questions?
 

Attachments

  • EMS.xlsm
    108 KB · Views: 10
Good day, Sir @vletm

I never thought of adding the list of vehicles in my report, Great Idea Sir, the colors, the list, the option, everything was jaw drop :eek::eek:

Sir, I think it was too much to add all the data in December, just few data to show example is just fine :)


Q)
Can I Edit the Departure and Arrival in the form? because I notice when I tried to enter the time it was set in the exact time and when I tried to edit, the debug pops up, I understand that this are just an example and it was nice, I think enabling to edit the time will be useful :)

Q) Does the tmps tab collects the time? or it was intended for something else? sorry for asking I'm just not familiar with all the precious things you applied:)

Q)If possible, if it's not too much to ask, can I have some video tutorials on how to do all the things you've done, some links and because I want to be awesome :)


Excited & hoping for the outcome.

Respectfully yours -

crenson313
Chandoo's Disciple
 
@crenson313
Yes, so far this is only sample and there can be 'some mistakes'.
Please, You try to test and let me know Your notices, okay?
A> 'December': There are only less than 1700 rows of data and this 'new version' isn't only one month use. There can be more more months. Of course, sometimes the oldes data can move to another like 'history'-file. No problem!
A> 'Departure and Arrival': yes & no, it can edit 'manually' but it's SLOW.
I'll do something for it!
A> 'tmps': It's just now for testing .. actually prev times limiters and so on.
A> Make questions ... give ideas ... I'll try to make code ...
with out videos... sorry. The code isn't ready, it needs modifications - cleaning.
Q> I need to know:
How would You use this (again)?
Do You 'printout Trip ticket'? and so on...
>>> I try to make this step-by-step ... Okay
 
@crenson313
1) I copied Dec-2015 data to 'Eturned'
=> One sample output (testing) in Sheet4
2) Vehicle's has challenge ... someone has written same vehicle many ways and I found only 89 (and still some of those are doubles)?
Check sample from 'Parañaque I'.
>> I gotta rest now, You try to test this.
 

Attachments

  • EMS.xlsm
    279.8 KB · Views: 5
@crenson313
I just remember one more ...
Do You use same 'vehicle type' same time with other 'Destination',
like 'vehicle ONE' drives 'routes Route A and Route B'?
If this kind of option is necessary, it needs ideas how to do it.
... keep on thinking!
 
@crenson313
New version,
> including 'Sheet4' chart - You can see weekly values too by clicking
> I gotta make some changes .. please, try to give answers of my questions.
Vehicles are not Route depend. You should check and get Vehicles... there are too many doubles and ... 250 missing.
 

Attachments

  • EMS.xlsm
    290.9 KB · Views: 6
Good day, Sir @vletm

Q)
How would You use this (again)?
Do You 'printout Trip ticket'? and so on...
>>> I try to make this step-by-step ... Okay

A)
We have this one whole sheet printed paper (Trip ticket). And they have supply of this for a decade, If I can actually show it here, moreover I kinda like the idea of having a printed one in this workbook but It's too much I guess :)

Yes!, I forgot to mention, I always keep in mind to have a blank copy of that same workbook, No data, everything is blank except the Fill up thing of course, so if I have to encode every month I always make sure to have the copy and paste of that workbook so for one year I have 12 Workbooks (Jan - Dec). And it will be Stored in New Folder named "Ex: 2015".

The idea of having "more months in the same workbook or moving old data in history file" is great but I don't know how is that even possible, Apologies Sir I don't know how that works, I think having a blank data in the same workbook is easy for me, just like what I did in my "December".

Q) Can I have a workbook without any data included? so if If I have to encode new month I can just copy & paste the workbook. Is it possible Sir?


Q)Vehicle's has a challenge ... someone has written the same vehicle many ways and I found only 89 (and still some of those are doubles)?
Do You use same 'vehicle type' same time with other 'Destination',
like 'vehicle ONE' drive's 'routes Route A and Route B'?

A) Yes, Sir, It is necessary, Some of the vehicles are used at the same time with other destination, vehicle one travels to Route A to Route B, but only when important travel or Ex. " the vehicles of Paranaque 1 can travel to other destinations, Yes I also called them doubles. The problem is that only the dispatcher knows when, where and what the vehicles they should use.

Q)250 missing?

A) Yes, Sir, The missing 250 are either in a Different department, currently in repairs, check up, Bidding, need for recycling or different areas. Yes, Sir in the month of December only 89 types of vehicles are used and in the next month, Different vehicles are going to use, they have some routinary schedule of using each vehicle, every month's or so they are checking up each vehicle.

Q)Sir, can I input the 250 missing data in vehicle sheet? because I tried to add all the missing 250 then suddenly the drop down of vehicles in the form is gone.


Q) Sir How can I Add destination? because every Month they are adding New Destination. Can it be easily applied?

I'm working on the pivot chart that you did Sir, it was beautiful and also the Chart, You did it again Sir. I'm trying to figure out how.

How did you do that table? I'm using windows 7 EXCEL :oops:

Sir, so far everything in the form is working fine except time, I tried to Edit the time but I failed,
I also encounter some bugs then I re-open the workbook again and test, there's nothing wrong I'll check it again Sir.



Excited & hoping for the outcome.

Respectfully yours -

crenson313
Chandoo's Disciple
 
@crenson313
1) 'Trip Ticket': If same kind of information as 'Form', why You want to do manually same? It's possible to add [Print]-button.
2) 'My version' file will be much lighter, no need to have monthly files. As I one time wrote, last 12 months data or so. Like, just beginning of this months, 2015-Jan data would be automatic to move to 'history'-file. Of course, there have to backups/different versions of all the time - if needed; all automatic! And one blank file just for sure. What is difference then day 1 comes or day 13? Nothing, it's much easier just keep on working, day by day, no matter what date - month - year it will be tomorrow.
3) 'Same vehicle for many Routes': I noticed that kind of cases. There were few times that one vehicle has two same times different Routes; two rows.
This is challenge now! Have to something! I have to check how to solve this!
Is two Routes per vehicle max?
4) 'Vehicles': Earlier versions has 'Route depend Vehicles', but there only one possible per Route. And many times one Vehicle has used with many Routes.
The newest version don't have 'Route depend Vehicles'; all Vehicles can use everywhere. That means, every morning there are max Vehicles in dropdown-list; all free and al little by little less, then Vehicles are marked to Routes.
300 Vehicle-list is too long! But it would be better to have all Vehicles on list, some are marked like 'AWAY' = not for normal use, including reason.
I have asked the list of Vehicles, that I can find out how to make 'easier to use Vehicle dropdown-list'. You can write all Vehicles to 'Vehicle'-sheet, just add new item to end of B-column. I guess that there were already doubles/triples like SVG-068 SVG-013-069 and SVG-13-069? Later, You can add/delete that list. You can write to column-C last action date/time and to colum-D if that Vehicle is 'away', 'broken' and so on.
5) So far 'all free Vehicle' are coming to 'Vehicle dropdown', but there have to be enough information before that!
6) Add[/delete] 'New Destination': so far, it's possible already, but I'll make it 'easier to do'. There is of course one limit now. There are limit of space for Destinations; 5*15 = 75. If You need more, even for future, I can add it now. Later, it will make much more work!
7) 'Sheet4': Did You find that 'weekly values' per Destination options too?
Do You need that another 'Doughnout'-Chart for single Destination?
Is this like Your 'Dashboard'-sheet? Do it need something to change?
8) What kind of report do You need?
(For me, word 'need' has different meaning than word 'like'.)
9) 'Time Edit Tip': activate cell => time comes => double click cell => edit => press <Enter>
10) As I have recommend, hopeful 12 months in same file. Older files will move to history. 'Trip Ticket' ... and so on.
>>> Is there something else that I should know?
Sometimes like 'minor changes' can make 'huge modifications'.
I gotta find out how to solve 'one Vehicle - many Routes'-challenge.
>> Try to give answers, hopes, wishes, NEEDS as soon as possible. <<
BIG-Q> You'll write ticket (not right side 'delivery per-pcs') and You'll save it with 'LEFT-save' to 'LOADED'. After 'Vehicle'll come back, You press [LOADED], select wanted row and press [Select], add 'Arrival-time' and 'delivery per-pcs' => press 'RIGHT-save to 'ETURNED'. => All done!
Something like this?
 
Good Day Sir,@vletm.

Kung Hei Fat Choi!

1) 'Trip Ticket': If same kind of information as 'Form', why You want to do manually same? It's possible to add [Print]-button.



A) because I'm not the only one who encode all this, I have co-workers and they don't know very much in Excel, they are older than me about 40's,50's & 60's, they encode everything manually they even used TypeWriter, some of them knows how to used computer but only basic and stuff. I share this workbook like my " December file" for them so that I can teach them how to encode manually and easily in my workbook and how to sort all things basically.



Q) What kind of report do You need?

A)Print button - Sir, I guess having printable output report is much better, like Printed report of Volume per Month or Per year, this is why I also have blank file and separate workbook each month because I create yearly report too,
and I manually encode every volume per month.
---------------

"Like, just beginning of this months, 2015-Jan data would be automatic to move to 'history'-file. Of course, there have to backups/different versions of all the time - if needed; all automatic! And one blank file just for sure."

Yes, Sir please.

----------------
Q)Is two Routes per vehicle max?
A) Yes, Sir, I like you max to three Routes, very helpful.

------------------
"Add[/delete] 'New Destination': so far, it's possible already, but I'll make it 'easier to do'. There is of course one limit now. There are limit of space for Destinations; 5*15 = 75. If You need more, even for future, I can add it now. Later, it will make much more work!"

Yes, Sir adding & Deleting Destination's will be useful.

-----------------
As I have recommend, hopeful 12 months in same file. Older files will move to history. 'Trip Ticket' ... and so on.

Yes, Sir please.Removing history or older data will be useful too.
--------------

BIG-Q> You'll write ticket (not right side 'delivery per-pcs') and You'll save it with 'LEFT-save' to 'LOADED'. After 'Vehicle'll come back, You press [LOADED], select wanted row and press [Select], add 'Arrival-time' and 'delivery per-pcs' => press 'RIGHT-save to 'ETURNED'. => All done!
Something like this?

Yes Sir that's right, this Idea is really helpful but if I can also change/ edit the date, departure and arrival will be more usefull because they pass the trip ticket to me and it's already Month's passed, ( Ex. they [ the drivers ] they already finish dispatching all sections for the whole month, let's say today is February 2016, and daily they must give the trip ticket to me for entire month of February 2016 but the problem is, most of the time they pass trip tickets reports that are already too late, instead they pass the whole month of February they pass January trip tickets, ( this is happening because as I already mention in the above .)

If I can edit the time , I don't need to worry anymore if they [ drivers ] pass the trip ticket late.
-----------------

"Please, check/test 'Vehicle'-sheet [Show]/[Hide]-buttons..."


Sir, apologies but what's the idea of this green squares? they have dates also.

---------------

"I would like to get list of vehicles, that I can make better drop down for those."

As you requested Sir, below is the attched Inventory of vehicles, you will notice, I modify/remove some data, for complicated purposes.

I also encode this report, please take a look in tab named" IDE-MV" MV for motor vehicles,and MC for Motorcylces, they also even use motor cycles for delivery, and Area 1 up to Area 9 and HO, "Body No." that is operational is only needed in the form.

Sir, All this data are too many , apologies sir did I mention 300 list before? all of that are just my estimates, You'll find many vehicles are you sure Sir?, that's why If I can add or removed vehicles in vehicles sheet in EMS workbook , I will be the one encode all this. because I don't want to pressure all Chandoo's :)

Excited & hoping for the outcome.

Happy New lunar Year -

crenson313
Chandoo's Disciple
 
Last edited:
I have been following along the thread here to learn what I can and I wanted to tell you I am impressed with your home page!
 

crenson313, sorry for the delay but

gateau1.gif

​
 
.
I have to ask...How did you get the cake to "move" and flash Happy Birthday?
When I've tried to copy "moving" .jifs in the past, they never maintain the moving once I copy and paste them.
 
You must log in or register to reply here.
Back
Top