Your own excel applications I've got an amazing mentorship program where I am taking you through step by step how to create an excel accounting application a comprehensive application we're going to cover invoicing inventory chart of accounts dashboards sharing sync so if that's something you want to learn how to do that on your own so you can have the freedom to work anywhere you like around the world I'm gonna bring you through that step by step in the mentorship program so have a look at.
That that you can find that of course at my excel mentor online you can do that my excel mentor comm have a look at that see if that's something you enjoy alright let's get started we've got a lot to cover out as you can see we've got a blank Excel application in front of us nothing on this sheet other than the fact that I've named it so we're gonna do everything from scratch so I wanted to take you through step by step I know you love these checks please keep in mind that every once in a while especially when I do these live.
Trainings there might be something a slight issue or issue in the code I'm doing the training so make sure you download the free application using the links in the description below I every each and every video I give these applications away absolutely free so make sure you if there's me run into any issues you can compare my application with your current application with the video because I'll always include an update if there are any issues I'll make sure to include those fixes and issues updates on the downloaded version alright so let's get started I want to.
Create a work order manager in fact it's going to be a mobile work order manager so let's get started on the design and basically what I want to do is give the user the ability to create work orders and send those work orders out to a mobile application so as we always do I want to save the first two calls for admin and we can eventually hide those columns so we're gonna color those columns probably down to let's say row 35 or 36 just bringing it down these columns are gonna be saved for our admin purposes and you'll eventually hide them so what I want to do is I want to just.
Color let's say the first three here I'm gonna add some specifics in those a little bit later on that's only for the admin use so eventually you'll hide these two columns and we're gonna put give it a title here mobile work order manager so that's the title I want to give it a giddy but a little bit of a different look on the fields so let's go all the way to o and then I'm gonna format these I'm not gonna spend too much time on formatting it getting it a basic format and just look and feel so nothing too complex here just to give us.
So you can see I'm gonna give it a fade out using two colors here on the top row and then the bottom and then the row below that will give it a subheading format the cells and just the fill effects of a little bit lighter of a colors so we're gonna use this light blue light blue here okay so we formatted this and now I'll give it a font title we'll stretch this out from let's say C to Al I'm gonna merge the set of those to give it a nice title increase the font here something a little bit larger and go to Calibri and.
Maybe italicize and bold okay so that's pretty much it well putting some icons a little bit later on I'm going to color these fields a light blue so that gives us the basic fade in background that's fine now I want to also create a it's going to be relatively simple but let's start out with an order ID maybe in d3 d3 ordered order ID because I want to track the order ID as well and I also want to have customer and then I want to have a status I want to know the status of a work order so those are important also maybe I want to know it's how it's.
Been assigned to that's really important because we may have different employees so I want to know who it's been assigned to let's put over here a sign too and then also want to signed on I want a date and time and then complete it on I also want to know it's been completed on so the work order that's important and also I want to give it some I feels a little bit larger fields we use merge and center on these specific fields so.
That we can easily enter enough information we were going to want to have enough information so we'll merge the set of those and left justify those and then I'm going to right justify these here so it gives it so we can see how it's coming together and I want to give it a format let's F format these cells and give it a border I want to use this blue border here and then we'll go outline here and then i'm using the - that's for the right and i'm gonna do the same thing for the left here so we're gonna hold the ctrl down selecting multiple fields right click and format.
Those cells and again just using the same color but this time we're gonna go the solid line border on the around okay so we get so we get an idea and we'll add these at okay so we've got a little bit of an idea of how it's coming together but i also want to know the requested work details and i want to know the work performed so let's add that in here and we'll start let's say on nine I'll merge and center this so I want to know request let's merge the center this and merge and center this so.
RELATED TOPICS:
We're gonna have here we'll call this requested work details so we can well have a
Larger space for that and then work perform so what the employees going to do is they're going to take their mobile application they're gonna enter what worked at performing we're going to bring that right back into this application so work performed and let's I want a larger area for this as well so we can go down to say 13 and then also the same thing here so that way we have.Larger areas and then we're going to do is merge the center this left justified and put it in the top and also color it give it the white color okay so we can see and then also I want to give some borders around here so we're gonna sorry it's off the screen format those cells right click same color border we're going to surround it with the outline color and then use a dotted line on the middle alright so now we can see our work order come but we also want to add some buttons in there and we're gonna add a table in there as well so I think.
Our tables should start probably on row 16 so let's add in some let's add in our table because I want to select from the work orders without order ID assigned to customer and then assigned on status we need to know the status of course we also want to complete it on requested work and the work performed okay there we got.
Now we got a let's highlight those and update those columns so we can get a little bit expanded okay yeah so we have to see so this is going to be the top of our table here so let's format it accordingly I'm going to scroll up right click format those cells and give it a little bit of fill same type of color fill effects so that we have the same theme and consistency throughout we'll give it a color like this and then we'll make the font bold here so we can stand out okay so now we can see we just don't.
Want to actually we add borders around that and we'll give it a border of that same blue color all the way borders around so we can see alright great so now we see our tables starting to come together and down here I'm gonna put all of the data in here all the data and I'm gonna bring down this blue color let's bring this down a bit and also this one I want to I want to surround the table with this blue color so everything is consistent okay great so we have that covered now and we can now move on to the table so we're gonna form at our table let's put our borders I'm not.
Gonna go down forever but you may wanna okay so we'll format those cells and then add a border on that as well and then I'm gonna add an inside border of a dotted line and then a solid line on the right okay so that gives us an idea and I also want to add conditional formatting on these so I'm gonna manage rule because I want alternate rows together color so I'm gonna use a formula I'm going to use the mod equals mod I have that on automated text because I use it so often so mod row beginning it and parentheses comma two.
Equals zero and I'll give that a light blue color so fill color about this but a little bit lighter so we're gonna go into more colors and just make it a little bit lighter that'll give us alternating row colors which will help us differentiate the data and I also want to do one other thing now let's set this up I want to know if it's a new work order or not so we're gonna new work order so this is gonna be true or false but if false for now and true for now and let's put a selected.
Row I want to know the selected row when we select a row I want them work order the load above so we can do that let's just put a teen for now and I also want to know the next ID remember having work order so next ID I'm gonna put that here and it's going to perform below so we have the orders I order IDs here and I want to know all the all the order IDs so we're gonna put this for example if I've one two three I want to named range for those work order IDs and that's gonna help us keep track of the work order and also know the next work order IDs so that's really important so how do.
We do that let's just create a named range on that formulas name manager new we can call it just call it order ID that's fine and we'll make it a dynamic so we're gonna use offset you can use index for non-volatile well so this is a small table so we'll just use offset sheet 1d we don't want that we don't want we're gonna increase the range but that d-17 is fine i just copied that comma comma comma count a what are we counting we want count all the ones from.
Let's say nine nine nine nine okay so we want to count all the texts so comma one that's gonna pull it out tab out tab
Back in and make sure the dancing ants cover our data which it does okay so what I want to do is I want to find the next ID because anytime we have a new order I want to put that next order here so how do we find that we can use the max you've seen used before max of what actually it started out with if they're equals in case you have no did it if they're max what does the max order ID of course the one we just enter but we.Don't want the maximum one at the maximum plus one because we want the next one but what if it's nothing what if there's no data in there well then I'm gonna start it off at one there would be an error if there's no max right so if there's an air if there's no data we just if there's no data at all delete that it's gonna default to one which is what I want so that's automatically going to set the first order to one which is exactly what I wanted okay let's add some buttons into that we also need that so let's insert a picture I've got some some saved here to make it easier so.
We'll add those and click insert and then I'll just set the height and 2.2 so that they're all about the same size except one of them is gonna be a little bit larger move them over here and then I want an icon for here called mobile work order so let's add that icon here gives it a nice icon then I'm gonna create button so what I want to do is I want a new work order I'll want to cancel new I want to save delete and I'm also gonna refresh the data because I need to bring in the data from mobile so I'm gonna have several buttons here let's create those buttons now insert.
Shapes and we'll insert just a rounded rectangle something like this and give it a height of let's say 0.25 and 1.4 that should be sufficient I give it a format something like this blue and also I want to write justify that because I want the icon on the left and then I want it in the middle so let's call this new work order okay and then let's.
Duplicate these new one for cancel one for save and update one for delete and one for refresh okay so we got all those buttons and they're not all going to be and then I'm gonna move all these to the top right because we just created new so I'm gonna take these all these format them and then bring to the front because I want them on top of the button so that's kind of important step this is the selection i have that on shortcut okay so let's update so i'm gonna refresh here i want let's say call check.
And update i don't want to check an update i want to look for any mobile any work orders that have been updated by it may be outside staff so check an update okay let's a format bet and I'll line it in the middle that looks good and then we're gonna group it and then I'm gonna create a name for it check let's just call it check Update button and that one's always going to be visible so that's fine we can put that or about here I'll place it in a moment and then I want to delete work order so.
Let's add that button here so we got delete let's just say delete to save some space W oh and we'll put the delete right here and then we can shrink the button a little bit zoom in to get the middle and bring it over here a little bit okay so we have the delete let's enter that and align it to make it look nice align the middle and then group it and then put delete work order button okay we have that that's only gonna show for.
Existing not new so we need to make sure and then I'm gonna use this one for the save and update work order let's call this call it save save or update recorder that's fine there and then I'm going to use this check mark for that alright and bring it a little bit okay so that's nice for that button we have that about centered hold the control group them and give it a name save update button okay so we have a name now we have one more two more actually.
Cancel new cancel new and kiss we're in the new mode we want to cancel that bring it over here that's only gonna show up for the cancel and bring it down here and then give it this icon right here that's gonna cancel new so you see how our our forms starting to come together and giving it a nice look okay so we can group those and I'm gonna call this cancel new button try to keep the same theme make sure we title everything and then we have the last one we have this new work.
Order so we'll bring this over here this icon again a format a line in the middle group them and give it a name new everything has a name new work order button okay so now we have new work order button and we can bring this out a little bit small okay so now we have new work order button we can zoom back out to 100% so you can see where we're at save and cancel new now saver updates always going to be visible new work orders and cancel new are not going to be the same in other words they're not going to be at the show at the same time.
Cancel new is only going to be displayed when we have a new work order however new work order is only going to be displayed when we have been existing so these two buttons are never going to show up at the same time therefore what we can just put them right on top of each other and we're that way the only ones gonna show up at the same time so they're not gonna be there delete work orders also only going to show up for new work order so we can put that here at for existing excuse me and check work orders so we have those updating them we can draw around it and.
Make sure they're all centered all right we'll make sure they're okay so now we've got all the buttons we're ready to go and let's take a look at what else we need to do we also need to have a shared folder I need to know the location of this shared folder so we can put that let's just put that in let's say a Mandan 3 shared folder this is gonna be the folder where our work orders are going to go so shared folder and I'm gonna put a space there and I also want.
To know the list of users users let's just say Fred Lisa and Sally okay so we've got a list of users because I want a list I want a list right here we'll just create a basic list here that's fine and I want the shared folder here so we'll color that white and give it abort so we know I'm just gonna paste it in I'm not kind of a Browse button because I've done that a lot of times and it's kind of basic so so it's kind of just easier especially on these live trainings where I'm doing this live I want to make sure that we get all the.
Features in somebody complained that my videos were too long my videos are not short if you're looking for short videos maybe maybe not but I want to we've got really you know complex problems so I'm gonna create comprehensive solutions to those contexts so that's my videos are always gonna be that way I'm definitely different in that sense so hopefully you like it because I always really want to give you tons and tons of value that's the idea behind these videos okay so we formatted we've got a list of users and then let's create we've got assigned to.
We can increase this a little bit we got don't have a lot of space there that looks a little bit better okay so assigned to this is going to be a list of users so we can do data and data validation we'll just keep it simple here I want a list I want a list here and the source is going to be these list of users right here Fred sell at least on Sally okay so we've got that I also want to shared photo location I've got a folder location in my Dropbox work or I'm just gonna use that and I'm gonna copy that location and I'm gonna paste it right in here into n3 so n3 that's.
Going to contain our location so that's fine n3 is where we want to put all those workers that we're going to send out to the employees very important because they need to get their workers in their phones fill those work orders out and then get back to and then it'll automatically come back so it's really a really going to be cool okay so we have that we've got the order ID we've got a customer and we also want to add some data mapping in here what I'm gonna need to do is I'm going to also create a template so let's call this inter work.
Order inter work work order and I what I want to do is I want to create a template and then send that template you know I don't want to send this to the individual users I want to create a really small work order template something very basic and they don't want to send that to them in a separate sheet of course it won't contain macros because I'm mobile you don't have any macros you can't there won't work so we're just gonna create a very basic sheet for them to enter and I'm gonna put that right here we'll call that work order call this sheet work order template and basically what this sheet.
Is just gonna be a list of the same fields but in a little bit different format so the first thing I want to do is create a title for that let's just say work order okay so it's gonna give it and it's just gonna be two columns it's gonna be really basic and that's the whole point I want this really basic because I don't want anything too too complex especially for the mobile because our end users are going to have to enter that data on the inside their phones so we got to keep it really basic and simple and then the same fields that we have here order ID customer status.
And assigned to and assigned on and complete on so we want all those fields and I want to put them in here just gonna drag them right here now and then we'll move them according to how we want them so we'll put order ID customer and status up here and then I'm going to put and that's okay I'm gonna put a sign to a sign on and control them and put those here so we have that and they don't want to put the requested work details here so we'll do that and I also want in let's say 13.
The work performs from an emergence Center of those so that means the requested works gonna copy that and I want to put the requested work details right here and we don't need that one I'm just gonna create a two column so emergence Center and then just these two is fine and then also I want to do the same thing for our so they can enter whatever work they perform something to copy this and then I'll update it I'm gonna I want to put that right here and again just two columns so that's fine merge and.
Center these two columns that gives them the ability to enter just a little bit of data so they can put their information in and then I'm going to go four rows for each of them merge and center these left justify update and then that should be fine add some borders around here and here I want to keep it simple format those cells I'm going to go off to scroll up right click format those cells give it a little bit of border same theme same basically idea around and then the dotted line in the middle ok and then we'll do the same.
Thing this is gonna be a very very basic very basic words just the whole point so that on their phones they can see it easily they'll get this they'll fill the information in and then they're going to update it and all they have to do is change it and then our application worker okay so we have order ID customer status assigned to a signed on and completed on they'll put in the completed on and they'll put in the work performed that's it that's all I want to do let's save this okay so basically what I want to do is take.
Whatever work order information is and when we save it or update it on new I also want to give it a status the status should be a drop-down list and it should be something like open pending and close so let's go into the data validation and hard code this three different statuses it's gonna be a list so we want open new work ours are gonna be open open pending pending is when they submitted the work order but it hasn't been closed yet and then close so those three statuses are going to be helpful as we code so we.
Have those options here and it's going to start at an open okay so we have order ID requested we're gonna add some data mapping here why do we need data mapping well I need to know order I D so let's add some I want the template data mapping and I also want the work order list so that means that I want to map this data to this particular we could use links but I'm getting in this case I'm gonna use data mapping so I'm gonna link all this information to.
This but I'm not gonna use links because we're not sure data mapping is a little bit easier in the code so let's do that so the best way to do data mapping here like let's say I want to map the template I'm just gonna take a screenshot using my screen shot software and I'm gonna copy and make sure to include the columns and the rows then I'm gonna copy this picture that I just took and then I'm gonna paste it right in here not this one I'm gonna paste it right in here because this is where I want to do the data mapping so this is gonna help me and bring it up a little bit so what now I.
Can see the data mapping now I know I know that work order ID right is going to be b2 I know that the assigned to is going to be b5 and I also see the customer is going to be b3 so we just continue on this I'm going to continue on to this and I'll do it all so you'd have to watch me okay great we're done so what I've done is I've just mapped this data on both the template sheet and the current sheet so you see III is the.
Order ID j-3 is the sign too so you see each one is map customer is located so I've mapped all that now we can get rid of this so now that they're all map we remember you can hide this all we need to do to hide this is just change the font here right here just change it to the same color as a background that's gonna hide all the mapping and you can lock those down and so your end user would not see this once we change it but I'll keep it open for now so that we can see it alright so I also want to use conditional formatting I want to know.
The selected row when user selects a row I want that to be highlighted in a different color so we can do that let's just highlight the entire table as we have it and add some conditional formatting I don't want to add a new rule and it's getting based on a formula and what is that formula it's gonna be equals whatever's in b5 which is the selected row equals row and then open and close parentheses and I'm gonna give it a specific format what is that format I'm going to give it a contrasting format so I'm gonna give it a font of white and.
Bold and I'm gonna give it a fill of a dark blue so a fill effects I'm going to give it a dark blue something like this and this this is gonna be a very contrasting color like this and click OK and okay so it's going to give it a white font with blue and it's gonna color whatever line so as we change this line it's going to give it and now the idea is when we select a work order from here it's going to display in the table above so we can do that as we build out this code okay so let's soon we're gonna.