You add sharing and sync in to this it can be shared in a shared folder across all deploys in a company and they can also have updated document management so that means when a document is created regardless of it whether it's simply time off or sales order or anything that you deem important for your company you can create this so that it passes through each manager each manager can then have their own revision they can write review notes and they can set their own status and.
You can also browse for any other file or you can simply move it to the next manager for approval just like that it's going to be a great training i've got so much to share with you in this application even if you're not really interested in creating a document workflow manager there are so many skills that you're going to learn in this master class including automatically created thumbnails including drag and drop including scheduling purposes or we have saving records data mapping and tons and tons advanced filters and tons more i hope you'll stay with us through the entire master class if you do like these trainings i create these each and every tuesday absolutely.
Free there's some great ways you can help us out simply by starting to subscribe don't forget to click on that notification icon bell we create these every tuesday so that'll get you notified of these brand new trainings each and every week this download application is absolutely free but using the links down below if you like to use your email or with facebook messenger but if you want 200 of my best applications i've got that available also in a single zip file that's that's just 77 right now and it also comes with an incredible library and that means a single click to open the application or a single click to simply view the original training just like we're doing right now that's an incredible library i hope you'll set that up i'm going to create these for you so let's get started we've got a lot to cover i'm going to go line by line code for code in this.
Workbook plus everything you need to know to be able to create your own document workflow manager or simply to create increase your skills to create your own applications in excel my goal is not just to make you successful with excel but to give you the tools and tricks and tips to give you all of the things that you need to get your career started in excel or create your own applications for sale let's get to it the best way to start with that is just to give you a general overview basically what we do is when we have workflow types we can create unlimited types of workflow types when we select on a specific document here all the information whether it's the document name.
The originator that's the person who first requested it and the workflow type we also have the requested on the date and the original file name we can browse for it we can open the document simply by clicking on here it's going to open it and that's regardless of the document we can then send it off to a different supervisor simply by either drag clicking on it dragging and dropping it or anything else so we can just move it along right here and it'll automatically move to a different thing or of course if there's no manager selected when we select on a document all the managers the supervisors in that entire chain automatically get displayed those particular.
Supervisors here are based on the document type so notice there's no supervisor because we haven't selected an originator the first person based on this original is that chain of supervisors so how do we know that well let's get into some of the fundamentals of this application and then i'll walk you step by step how we create it so we're going to start with the admin screen we have a shared document folder right we need to know where those documents are what we want to do if you use a let's say a dropbox your shared documents can be throughout your entire company that means every single user could get an application like this they can be shared throughout the entire company if you share the data using one of my sharing and sync techniques that i've just demonstrated.
In an older video you can then share and sync this application with them it'll all be synced up but we do need a shared folder so we have that here we also need a shared thumbnail folder when we upload a file we're going to automatically be cr able to create thumbnails so for example if i browse for a specific file and i have let's say this receipt here or a pdf or anything or jpeg and i want to create a thumbnail for it it's automatically going to create a thumbnail so when i save and update that it is that thumbnail which is this one right here that automatically gets updated based on the document that we have done so it's a great way to create those thumbnails we're going to be doing again on pdfs pictures or word documents even if you have a word document you can do that too.
So we need to keep those thumbnails in a specific folder so i've created just basically two folders here i've got our documents folder here and i've got our thumbnails here so our thumbnails are created dynamically they can be pictures or anything else and our documents are uploaded and copied so basically when we a user simply clicks here the browse what that's going to do is going to look for wherever that's located it's going to upload that so if i want to change that to a pdf i just click here pdf it's going to automatically change that to pdf save and update that notice.
That that thumbnail here got changed to a pdf just like that so we'll show you that so we've got those two folders i also want to know the company hierarchy who's the top of the chain that company vice president so we have in this is generally it starts out with an office staff which is the lowest employee and then it just goes up team leader office manager supervisor regional manager this is really important because we're going to have to know who's on top of who we also have dynamic workflow types really really cool because we can then create workflows based.
On that so for example maybe you want to request the time off maybe there's only two managers that are involved in this that has to be improved but maybe you have a large expense right like above a thousand dollars and we need to get that approved by several managers here so in that case this type of workflow type will be different so notice here when we select sales order we have a certain set of managers here we have a work order we have just a few different managers just five different managers or an expense less again five different managers and we have a larger one we.
Don't need this chairman here we have a larger one we have a project bids or invoice so we can create
What's called dynamic workflow types and we can do that inside the admin so for example the time off we just have these four managers we can then select which type of a manager again sales order would be large so here what we can do is we can select all the manager all this managers type at least they're positioned on whose supervisor it starts with the office staff then it goes to the team leader then the supervisor and then for the final approval the regional manager so we can crew.Not only can we create any workflow types we can then create the hot entire hierarchy of manager that has to approve that type and what i've set up to let's see what do i have about here uh nine or ten different ones here so it's really really great that we can do that and we can create that you can create more if you need it but i think it's a good start so then we have dynamic floats and then we have document statuses right what is the status what is the initial status the pending status when it's being reviewed once it's approved or is it rejected or it needs revision and maybe it's finalized right so once it's finalized we don't want it to appear in here right so if we decide we're going to finalize a document right we want to set okay we're going to set this.
To finalize we're going to save and update that we don't want that that document's now gone it's no longer here so only those pending documents that's going to keep us really organized so it's easy to finalize a document so we're going to have that too so that's it for the admin screen relatively simple we have our workflows which will be going to detail i've got a document database this is where all of our documents are stored right notice that one that we just said was finalized right we put that one that's finalized here but if we change that to pending review it's going to show up again into sales orders so our particular document list contains a document.
Id a document name the originator the original staff that created that we also have a workflow type right what is the type of the workflow that we're going to need to know because they're separated based on workflows so when i click sales order again notice that appeared back here because we now set a depending review inside the database we have the work orders right we have expenses we have notice that we can use receipts and pictures project bids and all and pretty much everything else we also have the quantity how many are here i want to have the quantity here okay so we have the.
Requested on so all the data the original file name and the thumbnail that gets automatically created where is that located i need the file name i know where it's going to be located it's going to be located in this folder here but i certainly need the file name i know the original file name the details or the notes of that and that's going to be linked up who is currently reviewing what manager is currently reviewing this what reviewer position what is their position right are they the office manager who's reviewing this and what is the current status of the document is it pending review has it been approved does it need revisit you know does it need more review or has it been finalized or revision right sometimes we may want to also send this back to the originator right it's got to go back to the origin or notice debbie was on the originator.
So if i want to send this back to the original i just click one button it's going to be sent back to debbie here notice i click on that again it's back to the originator or in this case debbie right debbie here back to there so maybe debbie needs to to redo it or something like that so you know it's been rejected and the manager can put some notes on that so single click to move it also again we can do use drag and drop we can move a document along just by clicking on it and pressing this this mark next to proof or we can send it back to the back manager simply by sending it back to the back so we can move these documents along the chain of the managerial approval process.
Simply by doing this now i've never actually worked in a large company i get fired from most of the companies that i work for which is why i work on my own so thank you very much sami for your advice and inspiration on this you helped me create uh at least the ideas for this and then i take it a little bit extra step so i don't know he knows how large companies act and so that that kind of helped me out so i kind of understand the process flow that so thank you sammy and we're going to continue on inside our document review now notice that there's multiple reviews per document so we need to track those in a different list here right so we've got that and we also have the revision file we can add a file onto that notes and so we can add a lot of information onto.
That if we want to okay we also are going to need to show that i need to filter that in other words i want to know all of the revisions or reviews for a specific document so we're going to need to run it through an advanced filter just like we do here so for filtering document 8 right document id 8 i only want those results going to come here and then those results are going to come into my workflow sorry so i know that these three types of reviews have been completed on this specific document that we've selected okay so that's why we need to keep track of this in a separate database i also have a staff list now our staff list here is going to come with an id staff name a position.
A supervisor if there is any an email which we do i think i'm going to think on our patreon platform i'm going to add email automation to this so if you haven't joined our patreon now's a great time to do that every single week i create additional features for these workbooks and a brand new training video something that maybe is featured or something that is a fix or something that maybe i'll take a focus on and i'll put that on patreon along with a whole pdf download and advanced trainings and a whole lot more on our patreon so i'll include the links down below if you want to join us there and possibly a picture i haven't really used the pictures but i think.
You know there might be a way we can incorporate staff pictures which should be kind of nice here right wouldn't it so maybe i'll add that in too you know into the patreon so workflows maybe i
Kind of run out of space but maybe a staff picture here i got a little space there something or maybe maybe a little uh circle staff picture here would be nice so i kind of put that in the database thinking maybe if you've got some ideas i'd love to hear them so at least we have a column for staff pictures and then i've got the row the team members that might be helpful moving on but nothing that we need to do so what we do is we want to know there's staff and we may want to put them through so we may want to run through advanced filter but for our purposes in this training all we're going to be using is basically the name the position and that's it.For this particular training then i got a blank email sheet that just gives you an idea of where we might want to go with this right now it's blank but if you want to see this become active let's go ahead i might put some templates in here so got lots of ideas for a patreon platform additional training all right so let's get into it so that's pretty much it an admin of workflows and two databases containing the documents and containing the reviews and then a staff list so that's it so how we go into this well let's start with the basics right just a few things that i want to know we've got some named ranges that i want to bring to your attention because those are going to help us both not only in the formulas but in the code so let's go over some.
Of the named ranges that i have created that's going to be in the formulas and name manager okay now these criterias when you see these these are created automatically by vba when we create those advanced filters so of course most importantly i've got a document id that is going to be in dynamic named range based on the document id so as we create them using the offset formula as we always do offsets can help us so as our document ids grow so is this list so document id we've also got document status i want to know the status of that and that's going to be here on the right side here current status so i want that in a named range that's very important because when i count.
Them i don't want i don't want to know which ones have been finalized right i don't want to count that i only want to count those that are currently working or in the process or moving up the chain anything that's been finalized we don't need to count so having that status is really important i also have the document type i need to know the document type sales order work order expenses that's going to come in handy when we count them i need to know how many sales orders that are not finalized and things like that so that document type will be critical again extract ranges those of course are going to come automatically through vba when we create those advanced final now i have one i've got to create a named range based on a single cell and that's going to help in vba that's.
Our finalized named range so that's just basically a final we know that final is cause a bit we also have the hierarchy that's very important because we need to know the company hierarchy and i also have one what's called needs review so if new excuse me needs revision or needs review either one would be fine needs revision we'll stick with that's going to be a named range for a single cell based on b25 same thing with pending here pending is also pending review that's helps us out on the code and once we get into the code you'll be able to see how having a named range on a single cell is very convenient and it's also much easier to read the code and we understand exactly by reading.
The code what that potential and of course reject it also we have that too so we have a few of them name range i also have some named ranges for the staff including a staff id which is also of course dynamic named range same thing with a staff name and same thing with staff position okay so we've got those named ranges and i also want to know the staff supervisor so those are name ranges based on that and i have a status right i want to know what the status is all of the document statuses name branch for that and then of course one for workflow types those are the workflow types in the admin again using offset so as our workflow types grow so does that so we can add and update.
Workflow types they'll automatically be linked here so how do we do that well we simply link it whatever is located here you see if i select the large range and admin d7 is going to automatically appear here so basically link this list i just basically copied this list and i pasted the links directly in here pasting those links that's going to link it up okay so but what i want to know is how many time off how many sales order i've got that quantity here that's going to let us know we need to know that so how are we going to count that well we can simply use countifs ifs.
Document types based on what d16 is right i want to know all the document types for d16 but not every single one of them because i want don't want to exclude those that have been final right if they're finalized i don't want to include them so the best thing to do is basically say count if all another criteria for the second would be the document status does not equal final this is that named range named range remember we have a name range called final so mark when i backspace this would start typing in we see it is that named range remember that's the same name range.
That i used right here so we click finalize we see it's final up here so that's why it's much easier using formulas much easier using code because we can clearly see it so it makes a lot more sense as opposed to connecting that with just a single cell where we might not know where that cell is so basically all we're using is counters that's going to let us know to count all the ones that we need right and if it's 0 we're just going to show nothing so that's how we count it's also based on remember all we're basing it on all the sales orders are all the ones for this but not including.
Anything that has been finalized right the status is not financed so that's it that's all we have to do here now i've got some information in columns a and b for the admin this is generally hidden and let's go over some of the fields here so what i want to know is when i select the document how many filled in fields some of these when i click a new document right i need to know i've got five fields that are going to be required document name the originator a workflow type a requested on now inside the code i could do if f3 equals empty or h3 equals empty or you know it's a lot of code.
Or what i can do is simply count the cells of those which contain text and put them in a number and we can use countif for that right so as soon as i add one it's going to count that and i've got that formula right here located in b1 what we're going to do is we're going to count a and that basically is count all the text of the following cells count the cells that contain the text so of these five cells we're going to count them right now we know we have one of them filled in if we select a specific document here we see that all five have been filled in and therefore it's 5. that way in vba all we need to do to ensure that those five required fields are filled in.
Is simply say if b1 does not equal 5 then let the user know so just something like that and we also use conditional formatting to call these yellow so that conditional formatting as soon as we see new document as soon as we fill it it's going to go to white that's using conditional formatting and basically we've just applied a single conditional format to multiple cells here in fact i probably just need a single one here and i'll delete one and double up the so basically this formula is just for these cells right and i'll include this one as well here so all we need.
To do is just select the cells here and just make sure that if they're blank cell contains a blank value then i want to call it yellow so when we edit the rule we see its cell contains blanks and we're going to format that yellow that's all we have to do right so as soon as we select something we see that they all go to white okay so that's it for that so we have that now we have a document id this is placed by vba when i select a document that document id changes we need to know that document id that is the id that's located here so if you follow any of my trainings this will.
Look a little bit familiar this combines a lot of training such as the kanban which we created a few weeks ago a great training it combines thumbnails which we created a while ago it combines drag and drop scheduling which we've created so basically we can combine all those skills together to make one very powerful application so we got the document id now i need to know the document row right i need to know that 7 id 7 is on row 10. so how do i know that well i'm going to use match for that so we're going to use if air just in case it doesn't match we're going to match b2 whatever's in b2 that's the id based on the name during this document it is and we want an exact match we're adding 3 because we know our first one starts on row four so we always.
Need that three because i'm not looking for one i'm looking for the row number so in that case it would be four if one was selected okay i also want to know the thumbnail remember for each document we're going to create a thumbnail it is that thumbnail picture that's going to appear inside this shape here i need to keep track of that thumbnail i'm going to put that in b5 okay i also want to know the original staff id this could be helpful moving forward so the original staff id is simply taking care of that we're going to use index we're going to index that staff id we're going to run a match based on that staff name located in h3 we're going to run that match based on the named range and we want an exact match in the column one if there's an error it's gonna show blank so that's gonna get us the staff id i also wanna know the original.
It's called called the originator row the original let's put in the word staff so we know that's the originating staff row and the originating staff position and their supervisor right so i want to know all those things and i want that's because that's going to help us moving forward so we want that all that information here to show up here so to get that row again we're going to simply use a match in this case we're basing it on the staff table so we're going to add 2 why are we adding 2 in this case when i find that staff list notice our first one starts on row 3..
So if our staff id is 1 i know that add 2 to get 3 that's going to help us with the row so we know and i also want to know what their supervisor is now i want to know their position we're going to use index i don't we don't necessarily need all these fields but they're very helpful for when we add in features so i want to know their current position what is their position so we're going to use we're going to run an index based on their position that's the named range we created we use a match based on that staff name that's going to get us their position i also want to know what their supervisor is remember inside every single staff that contains a position.
That contains a supervisor unless they're at the top of the supervisor and we also have email and picture and a bunch of other stuff so this is all for staff when we change the staff on here we know that we're going to automatically update that automatically okay so everything's going to update on that all right i also want to know some information for the staff that's reviewing that right the staff right if i change let's say we change let's say we got debbie and in this case her reviewing her manager tina james is reviewing that they're just set for pending review right it's waiting for tina to review that so we want to know their information so i want to know tina's uh staff id number using index match i also want to know the road that where stina tina.
Is located and the staff and what position she is and who is her supervisor so i want to know that as well okay i also want to know the selected review database when i select a review i may want to edit one of these reviews so i can select on it and it's going to show up here so that's very very important we need to know that so i need to know what row is selected in this case it's going to tell us that row in this case 5 is going to show up right inside here that is 17 this select review database is the database row that's located right so basically we save this 34 on a database here located in row 34. take a look at that seven right that's the id 30k hopkins.
And pending review so that is the database row where it's located if we need to make an update i need to know that review i need to know the database right so what is the database here so this keeps track of the days that way if i make an update right if i say test notes and i make that update it's going to automatically save when we update that on row 64. so we look at row 64 here and we can see that those notes have been saved right here in row 64. so we need to know what.
Row and column to save that in so we've got 64 and we know the row so that's very very important okay so we've got to have the database row now also we have the selected here's where we come in the selected flow row right what is the selected flow row when i select here i need to know what is our selected right remember we have different workflow and i've got conditional formatting that appears on these right so i need to know what row appears notice 16 17 18 right so we need to know what row because it is conditional format is going to help us and again i've got conditional formatting based on these two just simply based on the selected row so as you see here b15 equals rho that's the format that we've given that dark background along with the white bold font.
That is the one that we use and it's going to apply to d through e so it applies to everything all right so we need to know that row i need to know what type row two right notice there's a specific type this is one type row how do we know what that is right time off is one what i mean one here it's the first type second type third type right i want to know that because i'm gonna have to extract that information so i need to know the type row so that's the row that's located on basically two three four so it goes on and then okay selected review row this is where that review.
Comes in right here when we selected we just went over that that is the row that we're going to use if we select something that review row 6 because conditional formatting here is going to help us highlight that row b17 b17 equals row conditional formatting again help us recognize what row we select in this case it's b17 okay the last thing this is going to come in handy when we go through the drag and drop macro right when i select a specific group now i want to know the left position and the top position because if it's been moved i need to recognize that there's been a move i need to know that it's been changed and i need to automatically update and refresh it and.
Not everything so i'll go through that with you very very simple believe it or not okay so let's continue on so we understand the basic format of all this application right we understand that we can do that let's go over some of the basic types of macros and we have the ability to open again as we mentioned we can open any pdf just like that and we can also browse for new documents right so let's go over the macros and we have those both for the original document here we have the same similar macros for the revision if i want to upload a revision i can do uploader revision just like that and it's going to automatically upload that once i save and update it's going to save to that so each tip specific revision has that ability to so how are we going to do that.
Well let's take a look inside the vba developers tab visual basic or alt f11 is the shortcut there we've got document file macros these are the macros for the document file okay they're all going to use a very similar variable so we can dimension those all above here i need to know the thumbnail folder and i need to know the document folder right those are the two folders that we're going to focus on your documents and thumbnails we need to make sure that they're put inside the admin right if i need to save those documents and save those thumbnails i need to know what folder to put those in so we need to ensure that those are accurate so also i need to know the full file name and i need to know the full file path i also want to know what file type it is that's going to be helpful when i create those thumbnails i need to know what file type because we can also.
Create thumbnails based on word documents i want to create that on thumbnail i'll show you how that works all we need to do is browse for that we've got a word document here just click ok it's automatically going to create them for the word document and all we need to do is just refresh save or update that it's automatically going to refresh and we see now that that word document all the way down here this one right here is the one we have so this is the one that we just created when we open that we'll see it's going to open a word document automatically here and then that'll be that so we can even open word documents a lot a lot of really really cool features here all right so continuing on so how are we going to do that so we need the file type i need the thumbnail path.
I need the thumbnail name as a string right i need to know the thumbnail name and i need to know the document folder as a file dialog and the object chart is a chart because we need to create those charts that's going to help us create those thumbnails so the first thing what i want to do is run a macro right before this create i create a macro just to check for the photos i'll run this macro every time we do so we want to make sure that the user has uploaded proper folders in for both the thumbnails and the documents so the first thing the document folder is going to be based on d3 then we're going to add the backslash to it that's our shared document folder the directory the documents folder equals empty or the document folder example let the user know please.
Set a shared document folder in the admin screen okay also we're going to do the same thing for the thumbnail which is located in d4 we're just going to check and make sure that that has an accurate file path if not we're going to let the user know so that's it so all we need to do every time we browse is just to run this macro and the first one is i want to browse for the original document that is the macro that's been tied to this if we right click on any specific shape inside the group and of course we click assign the macro we see that is this browse original that we've created so browse original is the one we're going to first we're going to run the macro check for folders as we do each and every we're going to set the doc file as the application file.
Dialog mso dialog file picker we're picking a file not a folder so this is going to be file and with the doc file i want to assign a title browse for original document and we're going to allow multi-select notice there's no filter in here no no you know sometimes we add picture filters or sometimes pdf folders or text documents there's no filter we're allowing the user to upload any type of a document and that's really handy with this we're going to the file name is going to be based on the directory of the select item so what that's going to do is extract just the file name not the full file path just the file name this is the full file path right here selected items one that's the full file path but the directory of that is the file name only.