Learn How To Make A Mini Accounting Application In Excel Today – Full Masterclass

hello this is randy with excel for 
freelancers and welcome to the mini   accounting application in this week's incredible 
training i'm going to show you how to create this   amazing mini accounting application 
complete with purchase orders   estimates work orders and invoices along 
with an incredible transactions and we have   full inventory tying that up with an incredible 
dashboard and an amazing comprehensive admin   screen it's going to be an incredible training 
you won't want to miss it so let's get started all right thank you so much for joining me i'm 
really happy you can be here i've got probably   hands down the best application i have ever 
brought to you on a youtube video and i'm going   to share all that with you today so what is this 
this is an incredible mini accounting application   and i've got complete with work orders we have 
full work order management we also have purchase   we're going to be able to purchase inventory tie 
that into inventory we also have are going to have   credible invoicing course work orders we have and 
invoicing all tied up right we're going to be able   to add new copy to save update we're also going 
to have inventory complete adding new inventory   showing quantity on hand we've got that for you 
transactions be able to create any transactions   loading transactions searching and filtering 
for transactions based on multiple search events   and of course we're going to have transactions 
and a dashboard inside the dashboard i'm going   to show you profit and loss outstanding invoices 
and bill payments top 10 products to order   monthly sales graph of course based on a date 
range we're going to have a date range so we can   show you how to create a really cool date range 
changing that up and of course we've got a menu   here and a comprehensive admin screen that's going 
to help you set defaults and fully customize this   application including your own names your own 
ids and of course your own accounts right so   much to share with you on this training you're 
going to learn so much what are you going to   learn in this training well of course you're going 
to learn basic and intermediate accounting skills   we're going to go over all of that you can 
also learn how to handle database and record   management how to create records how to update 
them delete them we'll learn about that how to   create invoices purchase orders and of course 
their impact on the inventory so we'll be going   over that all that inside the order management 
here we're going to show you how to do that   and also we're going to show you data mapping how 
data mapping can reduce the number of lines of   code substantially so we'll be going oh complete 
over data mapping how to use that inside your code   also robust conditional formatting we've got 
a lot of conditional formatting we're going to   show you how to use that to make beautiful and 
useful applications of course how to duplicate   these orders we can simply copy this to a purchase 
order if we need to order those items i'm going to   show you how to do that so we can quickly create 
brand new purchase orders work orders estimates or   invoices on a single click and also dynamic named 
range we're going to be using named ranges for   throughout this application you're going to become 
an expert on named ranges and also of course   creating a comprehensive dashboard and how to use 
advanced filters combined with pivot charts to   create beautiful charts and graphs so we're going 
to do all that and a whole lot more i hope you'll   stick with us it's going to be a long training 
so make sure you grab your beverage of choice   take time to out and this is going to be 
incredible training even if you can't complete   watching this training it'll be always available 
on youtube or your platform of choice i'll make   sure it's always available for you you can return 
and watch it as many times as you want if i do   move quickly on this you can also slow down the 
video if you need to i did want to let you know   of an incredible launch that we're having i've 
got 250 of my best templates available for you   in a single zip file now available that's 2x 
nearly every single application and just today   now i decided to offer a brand new free bonus now 
this 250 was already built this pack all of my   last templates have already been in there but 
what i'm going to do is i'm going to include   this mini accounting application free as a 
free bonus to those people who purchased the   with pdf downloads i'm going to make sure that you 
get this accounting application absolutely free   template in there of course that's going to also 
be included in our template library that way the   template library is amazing because it's going to 
allow you a single click to open the application   and another click to view the youtube video 
training so you'll always have those resources   at your availability all right great we're going 
to get started right away on this training because   i've got so much to share with you on this 
i just wanted to make sure a few things that   you already have subscribed to the channel and 
don't forget to click that notification icon bell   that'll ensure that you get these trainings each 
and every week i make them available for you   please don't forget to share this video like it 
comment below i respond to each and every comment   so don't worry about that i want to hear from you 
this video training is for you as it is each and   every week all right so let's go over a quick 
overview now that we know exactly and then what   we do is we're going to get into the details so we 
have this really cool home screen this is going to   let us quickly and easily go to each and every 
aspect we have an admin screen that we're going   to be going over in detail on this we also have 
a dashboard as you saw here and then what we have   is the inventory you're going to be able to add 
edit update inventory add pictures and do all that   kind of stuff on the inventory now our order 
screen we can create multiple different orders   as you saw we can create estimates work orders we 
can create invoices we can create purchase orders   and all of that each one of them look different 
each one of them are fully customizable we'll be   getting into all of that and of course we also 
have transactions right we're going to be able   to create multiple transactions we're going to be 
able to search for those transactions either by a   specific account or we can search by a particular 
amount if we want to or we can search by a name   right we have that available as well so there's 
a lot to choose from here and there's a lot to   show you on this really incredible accounting 
application has everything inside that you need   to cover your basic accounting and i'm going to 
show you every step so that way not only will you   have a template but you'll able to customize this 
and create it on your own and be very successful   at it so we've got these transactions inventory 
and orders all right so that's the basically it'll   tie it up with an admin screen and then of course 
the dashboard which you have seen along with the   date range all right so what we're going to do is 
let's start out with the admin screen we're going   to go over what's inside the admin screen and why 
that's important to you now any good application   right every business everybody runs their business 
slightly different they have different names they   call work orders differently for example 
estimates maybe we want to call estimates   maybe we want to call them proposals right so what 
we want to have is create a dynamic application   in which they can change their own words and then 
have those that vocabulary used throughout the   application so if i call instead of an estimate 
i call a proposal because that's what we're used   to in the company that i'm working for then it is 
that new vocabulary that gets used throughout the   application so now we have proposal up here we 
have proposal status we have add new proposal we   have copy to proposal so it's automatically added 
throughout the application so the application is   fully dynamic whatever you change inside 
the application right here in the admin   and settings screen that's going to be changed 
throughout the application so we have a lot of   dynamic vocabulary here including invoices and 
purchase orders so whatever you call them and   here's just some ideas of what you might want to 
call it work orders could be called work requests   invoices receipts bills or orders purchase orders 
or purchase or pos whatever you want to call them   and the same thing with our customers if we want 
to call them clients or customers or vendors or   guests or whatever we want to call them we can 
simply change it here and they're going to be   changed throughout the application automatically 
and it's also going to update the database as well   and we're going to show you how to do that 
so that when we make changes it gets updated   throughout the application very very helpful 
naming especially when we use other languages too   also moving down we have uh accounting here 
when we create transactions we want to create   both income and expense types transactions 
in expense type can be cost of goods sold   cost of goods solds are those type of expenses 
and which are directly related to the work that   you perform so if we are creating if we're sending 
technicians out and they're doing some repairs and   they bring some products with you the hours that 
they perform along with the products that they   use are all cost of goods sold those expenses are 
considered cost of goods sold while other expenses   such as lease utilities or meals entertainment 
something that cannot be related directly to the   work or products being created those are called 
other expenses and that's going to really help   us when we create that profit and loss inside 
the dashboard we want to show income cost of   goods sold of course we've got a net loss in this 
one gross profit and other expenses we want them   showed sep those expenses cost of goods sold and 
other expenses shown separately so having those   two different types of expense accounts are really 
going to help us detail down into the types of   expense a company can have and of course we can 
create as many expenses as we want okay also now   we take a look up here we have a status now each 
individual orders can have status in other words   this is fully customizable an estimate can be 
pending completed sent while a work order can   have its own statuses invoices can have their own 
statuses and purchase orders can have their own   status and let me take a look and show you what 
we mean by that i've got to add a home button   onto this i think that's kind of important so 
let's go ahead and do that now so let's take a   look at this i've got a home button that's going 
to allow us to do that admin screen needs one too   i thought we put it there okay so we got one 
for the admin let's put a home button and you   see these going to automatically go to i'm going 
to show you how to create that okay so insider   order management if i have a status here if we say 
proposal status so let's go ahead and add a new   estimate or work order or proposal or anything 
like that what we want to do then here there we   go an estimate the way we had it before estimates 
can have certain statuses pending completed sent   pending approval approval or denied right 
those are fully customizable statuses   while work orders here can also have their own 
statuses assigned on the sides right so you can   see how the status the drop down list the dynamic 
are completely customizable pending unpaid and of   course lastly the purchase orders can have their 
own status right so here ordered pending shipped   receivers right so you see the same cell can 
have dynamic dropdowns i'll be showing you how   to create that and that's all going to be based 
on what we set up in here and also what we want   to do is we want to have a setup right we want to 
have a default so if we want to know an estimate   if we want to set brand new estimates we can have 
the sent as a default and maybe we want our work   orders to automatically be assigned that is the 
default status when we create new estimates or   new work orders so when we go back into the order 
management and i decided i want to create a brand   new estimate we see that the estimate status has 
automatically been sent the set is the default   and we could a brand new work order we also see 
that we have a work order status as a side those   are completely customizable from our admin screen 
right here right so we have our default settings   and that means for brand new orders so invoice 
estimates work orders so everything is fully   customizable and setting dynamic i'm going to show 
you how to do that creating this dynamic not only   do we have a dynamic data validation based on the 
type of order that was created but we also have   the dynamic default so we can set the defaults 
with a single check so we've got a lot going on   there i also want to count defaults right when we 
create an invoice what income account notice we   have a list of income accounts what income account 
is going to get affected i want to know the income   account so that we can automatically mark those 
invoices when we create an invoice likewise when   i create a purchase order and i purchase materials 
i want to know what is the default expense account   and why is that important when we go into let's 
say we go into invoices i want to set a default   invoice account that default income account now we 
can change it on individual invoices but i want to   know the default income account associated with 
this invoice so when i create those invoices very   easily it is that particular income account that 
is going to get created credited likewise when   we create a brand new purchase order i want 
to set a specific uh actually this purchase   account this should be invoice account income 
this should be expense account i'll update that   shouldn't be invoice account it can be expense 
account but we want to make sure that this is   dynamic so i'm going to make that update in other 
words i want to show expense accounts if it is on   purchase order or if it is so let's go ahead 
and show you how we're going to do that so   we're going to do equals if right and i want 
to know if our order type i'll show you what   the order type is the order type is very specific 
to whatever order type or we offer the name range   equals and i want to know if it's going to be the 
invoice type invoice order so this is going to be   if it's an invoice order i want to show this as 
an income account otherwise if it's a purchase the   only two particular orders that we're going to use 
this for purchase orders and invoice and then it's   going to be expense account expense account we're 
going to show you what those types are and the   name ranges will go over it and that's it that's 
all we need to do so that way automatically this   is showing expense account for purchase orders 
and then anytime we load a brand new invoice it's   automatically going to show income account okay 
great now just keep in mind we'll be showing over   that order type is right here called invoice as we 
add a new one right it's going to change so keep   in mind that this purchase order here if we take a 
look at this this is order type so this particular   cell is the named range that we're using okay now 
back inside the admin let's continue going over   we we looked over here but we've got some named 
ranges here this is called the estimate order   we're going to be using named ranges throughout 
the application we'll be going over them every   little bit but this is called the estimate order 
this is called the work order this is called the   invoice order that we just used this one inside 
our formula invoice order and lastly we have   purchase order right so those are the named ranges 
that we're going to be using also i have customer   name i have the vendor name i have the employee 
name yes we'll be adding employees items and   customers and vendors kind of didn't even show you 
that my goodness right when we add a brand new one   we can add a customer vendor or employee so we can 
do that whoops forgot to show you that well now   you know see there's a little secrets keep in mind 
that purchases are going to have a list of vendors   while invoices are going to have a list of 
customers right so this is going to change the   customer we have a dynamic list of customers 
right we have a dynamic list of employees   however all of them are either any one of them can 
be added here if i select a specific customer here   we want to may want to edit that customer we can 
do that through here we're going to be getting   into detail on that let's continue with our uh 
just because i forgot to show you that so we've   got customers vendors employees and items we each 
one of them have individual names this will help   refer to them inside our of course formulas and 
help us also inside the code so we went over a   pending status now account defaults we understand 
the transfer account i'll probably set this up   this is going to be a default so when we create 
a brand new transfer i just didn't get that but   i probably should if i create a brand new transfer 
here's a transaction it should be transactions and   i create a new transaction do we want to set a 
default account what account do we want to set   default is if i were to copy this here and then 
go into the admin screen and i paste it should   say transaction accounts and i paste that entirely 
there and i call this transaction account let's   use to do this abbreviated right i want to set a 
default one animal transaction account so i want   to set maybe we're gonna maybe we want to leave 
it blank but maybe we have a continuing maybe   we purchased a lot of meals and entertainment 
so we want this is our default account we have   other spaces here we can add to that so maybe we 
want to do that too the transaction account so   that way when we create new ones and i'll program 
this in with you of course when we create brand   new transactions it is that account that i also 
want to set here in the add new in jail so we're   going to have transactions so that's very cool 
so we've got some default accounts set up this   is used for invoices this is used for purchases 
and keep in mind that if i invoice income   purchase expense and transfer okay we also have 
tax details i don't want to know the sales tax   both when we create invoices we're going to create 
a sales tax but maybe there's a chance that when   our customers when we purchase on a purchase 
order our tax rate could be different   so let's say we have a sales tax of 10 percent 
i'm not sure why they would be different but   at a purchase tax rate of five percent we 
may want to call them something differently   therefore when we go back to the order screen 
if i go to a let's go to purchase order here   let's say three is a three is a purchase order 
right so if i've got a purchase order here i   know our purchase tax is five percent however 
if i create an invoice here it is an invoice so   in invoice i want a sales tax at ten percent so 
notice that the tax can also be dynamic based on   whether it's an invoice or based on whether 
it is a purchase order so those are the two   transactional types of orders estimates and work 
orders do not have any type of transactional   therefore when you see an estimate right there 
is no account when you see a work order there   is no account assigned because only invoices and 
purchase orders have account related so that only   they can have payments on them and only they 
can have expense accounts or income accounts   okay so moving on so we've got the accounts now 
we also want to set some terms right if i want to   if i know that we create invoices or pay bills 
based on net 30 or net 15 when i create a new   invoice or a new purchase order i want that 
default set up so that we can automatically   set the payment due date right so that means right 
if i create a brand new order here and i want a   new new invoice and i want to set that default at 
net 30 i know that our as soon as i decided let's   say i decide to change this to six five i want to 
make sure that our due date is automatically based   on net 30 if i decide to change these terms to net 
60 that due date's automatically going to update   right and it's going to be the same for purchase 
orders if i create a brand new purchase order   i also want that automatically due date probably 
set it automatically when i create a new one   based on assuming that there is a purchase 
and assuming that there is a payment or date   the due date should be set automatically but of 
course it can change right and that means you   have 15 days to pay that bill and i want that due 
date automatically set based on the purchase date   so that's when of course we can set a default 
right that default is going to be for when i   create a brand new invoice what payment term do 
i set as default here if i were to go back into   the admin screen and change that to net 15 any new 
purchases or any new invoices would automatically   get that net 15 here so we're going to set that 
as a default that makes things a lot easier for   us a date range this is very helpful this will be 
used in our dashboard when we create a date range   whatever date ranges we have this is what we 
want to be reflected inside our dashboard so   we go inside our dashboard and i change this to 
this month right i want our data to automatically   be reflected and i want our from and two days to 
automatically show that if i change it back to   this year again i want that from and to date 
automatically updated based on the date range   now that date range is basically set up directly 
inside here so we can set up those dates here so   we're going to be setting up and we just basically 
set them up our basic formulas from the year of   today if we want to set up for example from the 
beginning of this month we would just use the   date formula from today the year of the current 
year of course the current month and of course   one setting that first and then we use the same 
formula except we wrap it around eo month and   that's going to be the end of the month meaning 
the current month so we've got some formulas here   you can take a look at them we won't be going over 
to too much detail on the formula they're pretty   uh standard okay this is really really important 
we've got an accounting basis now some businesses   consider income created when they create an 
invoice or when they create a bill however other   companies use accrual based account excuse me use 
cash basis and they assume that income is only   when they get paid or when they actually pay when 
the cash leaves their pocket right or when the   cash comes into their pocket that is a cash base 
and we should be able to do both of these right so   we just simply select this option and that's going 
to affect our profit and loss i'm going to show   you how that does it so we go into the dashboard 
right we're going to see that that's going to be   affecting so if we refresh this you see our income 
automatically changed right look at that the   income completely changed based on the accounting 
basis now this is really important cash basis   counting means that it is income only when you 
get the money is expense only when you actually   pay the money whereas a cruel based accounting it 
is income as soon as you create the invoice or as   soon as you create the bill now personally i kind 
of like cash because we know that when we create   bills when we create invoices customers don't 
always pay 100 and they always don't pay on time   so i personally like cash basis but of course 
it's whatever your company is used to whatever   you're used to and or whatever your your rules 
are sometimes government has specific rules on   on which you can use but there but both are 
available inside this application which is kind   of really helpful also we have item picture folder 
as you saw here inside the dashboard our items our   inventory items have those pictures right those 
are located inside our items database here we've   got a database of items basic information item 
id inventory type sales purchase description   work or service order description that means 
uh when we make a sale or an estimate there's   a specific description for items when we make 
a purchase there's a different description   or when we create a worker you want to send our 
staff on a specific work order we have another   description we have a minimum quantity for a 
product a sales price and a purchase cost and a   picture associated another picture associated are 
based on a folder that folder locations right here   so i've mapped that specific folder location right 
to here when you get this file of course you can   map your own and if you want all of my pictures 
and icons and every all the other resources   our patreon platform will have all 
of those resources make sure you   join our patreon i've got that link down below 
we've got brand new updated videos updated   templates each and every week along with a ton of 
other benefits so i hope you'll do join us so we   can browse for a picture folder whatever picture 
folder we want and we could just place that in   there okay also again we also have dynamic footer 
messages now this is really cool now what is a   footer message a footer message is a message that 
shows down here thank you for your business right   we can set different ones if we want to we can 
set a brand we have a list a footer message   maybe 30 days right so estimate is good 
for 30 days so what we can do is create a   multiple footer message as we change our footer 
message here it's going to change down below   however we want to set up some default footer 
messages so that means when i create a brand   new estimate i want to set that footer message 
as 30 day message and it means this estimate is   good for 30 days on the estimated date or perhaps 
when i create a brand new work order i want to set   a footer message of cleanup letting our employees 
know please make sure to clean up the entire work   area before leaving and of course likewise when 
i create an invoice i want to thank the customer   and when i create a purchase order i want to know 
about letting the driver know deliver all packages   back to her so we can create any number of footer 
messages and we want to have those footer messages   and set those defaults here so that includes a 
message name and along with the message text but   what i want to do is i want to set the default 
on a per order basis that means our estimates   are going to show this if i want to change our 
estimate so that our estimates could show delivery   times i don't know why we'd want to do that we 
can set the default here so then if i create a   brand new estimate and it's going to show that 
default so let's go ahead and here estimate here   and create a brand new estimate here it's going 
to show delivery times and then delivery time so   the default is automatically set on this as i said 
this is a massive application so we'll go ahead   and reset that default so i want to be able to set 
that default i want to set the default work order   footer message the invoice and the purchase 
order all should have default estimates that's   really good i believe we've covered everything 
in the admin screen it's the most detailed   i've got a few named ranges that i just want 
to bring to your attention that are coming   from the admin screen so we're going to go into 
the formulas and we're going to go in the name   manager and we're going to see accounts expenses 
right so we've got a dynamic named range based on   the account expenses it's going to be based on 
all of those expense accounts we also have one   let's bring this over here we also have one called 
income right based on our income accounts and i   also have accounts accounts expense type right 
i have another name range for expense types so   this way when i look up a specific expense i can 
easily use index and find out what type of it is   is it a cost of goods sold or is it an expense so 
that's going to be helpful in a formula so we have   expense types and i also have all accounts sorted 
now this is going to be a sorted account that we   can have all the accounts sorted and that's going 
to be located on our transactions let's go ahead   and go to that so i can show you that and that's 
going to be coming directly from the transactions   here so if i move over here let's unhide 
columns a and b so you can see a list of those   our transactions uses all accounts right we can 
have both income and expense accounts inside this   account so what i want is a unique list of all 
the accounts so here's a unique list of all the   accounts called unique accounts so we can create 
those automatically and i'll show you how to do   that so that's going to be all accounts all right 
great so we've gone over in some of the named   ranges inside here we also have some dynamic named 
ranges for our order status that's going to really   help us out inside our vba code so we can quickly 
get to the right automated status for each based   on a form along with the default data so insider 
formula and name manager and we see status here   we see we have one called status estimate here 
and we have a dynamic range called invoice here   so we move over here and we have one called 
status let's take a look at the work order here   right which is over here and we have one more 
of course called status purchase orders and we   also have one for the default so you see status 
purchase order right here and i also have one for   the default i want to create one for the each one 
of the defaults so that's going to also help us   so if we take a look inside here we see moving 
over here we have one for the default i've got   a named range for the default it's called status 
default estimate and another one for the invoice   and another one for purchase order and merge 
so each one of these has its own named range   so we have one for the status and one for the 
default status okay so that's going to easily   allow us to locate which one is set to fault when 
we create those brand new estimates work orders   invoices or purchase orders keep that in mind that 
also i've estimated this is c5 right of course   this is also i've also created a named range so we 
can do estimate order that works as well because   we've created a named range for those same thing 
with work order we i like to use these in formulas   so instead of using a specific cell we see it and 
that way when we make a change to work right if i   was going to change this to work request you would 
see that that would automatically change here   up and there so that's kind of nice i like that 
to have that kind of dynamic ability there inside   there so using those forms and the same thing 
for invoice here i've done invoice orders we   can just quickly find it invoice order and lastly 
purchase rows so we can see how these name right   support store oops i didn't do that that should be 
purchase order okay so we can see how using these   uh named ranges here fully customizable vocabulary 
throughout the application and then we can just   use that so we see inside the formulas using the 
name range we know exactly what it is great so   that's kind of a rundown of our admin screen we 
can kind of see just about everything else and   we've got this i've also got a named range called 
item picture folder here for that so in case we   have a named range for footer messages let's just 
quickly go over that because that's going to come   up so we'll take a look inside footer message i 
have one for the name and i have another one for   the text so we've got two footer messages and i'll 
remind you these as we go because there's a lot of   named ranges but one for the footer message name 
and one for the text so we've got two of those   okay very very good let's go ahead and take 
a look inside some of the database so i can   just familiarize yourself with all the data the 
first thing we have is our orders database right   this tracks all of our main order we have order 
id the type of order it is as you know we have   four different types of work orders the order 
date assigned what is the status of that remember   we have dynamic statuses the name associated 
generally it's going to be a customer or vendor   name right vendors for purchases customers for 
estimates and invoices and probably work orders   and then we have an employee list now the 
employee of course comes directly from here   right here so we can assign an employee to any 
type we also are going to have the terms right   the due date of course work orders wouldn't have 
a term estimates generally wouldn't have a term   but invoices and purchase orders would have terms 
in the along with the due date our footer message   named the name of the footer message so we 
recreate it if there's been a payment invoices   and purchase orders would have payments whereas 
workloads and estimates would not again invoices   and purchasers would have accounts while estimates 
and workloads would not have an account associated   with that what is the total amount of that we 
can have that total amount work orders generally   wouldn't have total amounts but estimates invoices 
and purchase orders would have and then we have a   balance here what is the balance based on the 
number of payments along with the total right   so we have that and that's going to come from a 
formula here i'd like to create these dynamically   so that we create a formula and i'm going to 
create simply basically the total amount minus any   payments that's our formula which is l then when i 
run some reports i'll just bring down that formula   okay now we have order items database if we take 
a look inside an order any one order we have items   associated with an order right so i want so for 
example i've got three items associated with this   inside this purchase order we've got 
items description quantity price and total   i've got to store all that information 
somewhere and so where are we going to   be storing that well that's going to come in the 
order items database and the order items database   is going to store we certainly need an order id 
notice order number one has three of these items   it's a work order we got the order date the 
assigned if there's an account that can help us   work orders wouldn't have accounts the item name 
the description of it along with the quantity   the amount this wouldn't necessarily come for a 
work orders but it could be helpful in the future   and in a row that's associated this is the order 
right it's on row number 10 row number 11 or row   number 12.

So i want to know row so that way when 
i bring it back in we know exactly what row was   on before and we also want to know the existing 
row the database row this is very important the   database row 4 5 and 6. so we're going to use a 
formula for that formulas are important because if   i delete one row the formulas will automatically 
update so we want to use formulas there also the   item database right we saw that we have item 
information well that's all got to be stored   somewhere so we have that inside the item 
database and it's going to start with the item id   the item name the inventory right we can 
have both inventory and non-inventory parts   hourly service work would be a non-inventory part 
meaning we don't want a stock inventory it's just   a service item so we don't stock inventory for 
those types but other types of inventory we do   our sales or estimate description along 
with a purchase description and the work   or service description we saw that before we 
have a minimum quantity the sales price the   purchase cost and the picture we went over that 
already the transaction database is very simple   right that those are all going to come from these 
transactions right and that's going to come right   here we have a transaction id the date of the 
transaction the account type what name was on it   a vendor name customer name or employee name the 
account that's associated the amount and a memo   and the names database now this is really cool 
because we can store customers vendors employees   all within a single database as long as we know 
the type of it so we have the name id the name the   address city states it phone email and a status 
right that's all going to be very helpful and   we're going over that okay great of course names 
as mentioned before we can quickly edit or update   names using these right if we want to add a new 
one right we can just clear this out delete this   and we can add a brand new name here if we want to 
add a new name we can create a customer a vendor   or employee and we can create a status by adding 
all of that we'll be getting into that okay so   that's it the database really is just made up of 
five different databases again orders order items   items database transactions and named database so 
it's very very simple on these four i've kept this   very clean and while it is robust it is very very 
simple to keep it nice and clean so we've covered   the admin we know it's we've covered the database 
now let's go ahead and take a look at some of   the simplest screens let's go ahead and go inside 
the item screen the item screen is basically this   we've created a brand new items screen and what i 
want to do is be able to simply select an item and   then have that item load i may also want to load 
it up automatically or i may just want to start   typing in that name and have that name come up so 
if i want to type in don right i want that to come   up automatically now how do we do that that's 
called autocomplete now autocomplete's a great   feature and it's very very helpful so if i type 
in let's say jacob's coffee here jac it's going to   automatically come up however for something that 
has like coffee beans let's take a look at this   coffee beans la vaza or coffee beans crema it's 
going to take a little bit more because those   names are very similar so i can type in cof coffee 
beans and nothing's going to come up as soon as i   type in something that's unique it's going to come 
up how do we do this well to use autocomplete what   we want to do is we want to have the list of items 
in the same column now if you take a look at this   you don't see it here right it's here though but 
what we're doing is we're hiding it it's actually   right here let's take a look at this you see 
the formula right here that is actually hidden   so i do have a list linked to items let's take 
a look at this items db and three what is that   well inside our items database what i do is i have 
a list of sorted items and inside here is called   sorted item names when i create a brand new item 
we're going to automatically update this list and   make sure that they're sorted now to get this 
list what i want to do is i want to take this   list and i want to create links so that means 
any updates to this list is automatically going   to link so what i've done here if i go down all 
the way let's say to about right here holding   shift control and then i set a format i'm going 
to go into the let's say home here and notice   that there's a custom format if i change this 
to a general format you're going to see them all   here so we see we have the names here that's 
great but it doesn't necessarily work let's uh   i'm going to clear this something out i'm going 
to delete what's here because it's getting called   so now if i type in and nothing's going to work 
let's type in d-o-n notice it didn't work right   why didn't it work now because i deleted something 
very very important okay what we have here is   called i went over to specific training called 
don't break the chain right this actually has to   be a link a chain link between these cells that 
link with data now that data can be any type of   data even spaces so if i added let's just i had a 
lot an x here and if i want to add an x or you can   use a new space but i'll use x so you can actually 
see it right and i'm going to paste those values   in here and then i'm going to paste the value 
here we've now created a chain take a look at this   linking of full text these cells now it doesn't 
have to be the same so now when i type in d o and   notice it comes up automatically right so again 
e s let's do something espresso coffee so notice   but if there's a break in the chain right if 
there's a break in this chain if i delete that and   that chain gets broken it will not come up right 
so notice it didn't come up as soon as we fill   that chain in with values any value at all it will 
come up so that's called don't break the chain   now those of course we don't want to see all these 
x's we can also use spaces so let's say i'll use   two spaces right and then what i'm gonna do is i'm 
gonna copy this i'm going to paste this down here   paste in those values here and i'm going to paste 
the values here so now we've replaced it with   spaces and now let's check to see if it works it 
still works great right so that's perfect that's   exactly what i want and also you can do this 
you can do let's say es or let's just do e okay   and then i'm going to hold the alt down arrow and 
that's going to help us also quickly get to where   we want to go using what we want so using alt down 
arrow also helps too if you want to quickly find   out where you are so that's really cool so we 
have that available to us so i really like that   and it's called don't break the change now we see 
but we don't really want this ugly uh text here   we really want it hidden right we don't want to 
see those so i'm gonna hold the shift and go all   the way down to the bottom and i've got about 
a thousand that we're using right so a thousand   and what i'm gonna do is i'm gonna format this i'm 
gonna go to the home i'm gonna give it a custom   format i'm gonna go into more number formats 
and then i'm gonna go to custom here and i'm   gonna use three semicolons three one two three 
three semicolons that will automatically hide   it everything so now we have auto complete that's 
working just great however we don't see the spaces   we don't see that chain and we don't see the 
links but it's all there so that's how we use   autocomplete very very powerful especially when 
we need to load we have a large list and we want   to be able to automatically set those and create 
quickly created without having to scroll down a   large list and keep in mind if you have a row of 
thousands right it's going to take a little bit   longer to load that list so keep that in 
mind that the lower down on the list you go   a little bit longer it's going to take keep that 
in mind so if you click on a cell it doesn't come   up automatically list with thousands take a little 
bit time depending upon the speed of your computer   okay great so we learned how to do auto complete 
now we're going to now you see as i change it's   going to load these up how do we quickly load this 
information up of course that's going to be in   vba we'll be able to add a new item add a new 
picture if we want to browse for a picture   clearing the picture we can do a lot of that so 
how are we going to do that well i don't need   to say this let's go ahead and okay so it's going 
to come up saving an item deleting an item that's   going to of course all be inside so we've got 
a few macros to go to inside vba if you wanted   to learn how to get into vba we can go in to of 
course developer if you don't have this developer   open we can display the actually go into the 
options here and then just go into the customize   ribbon and make sure that you've selected a 
developer if you are new to vba i have a great   course available to you actually not one of my one 
of my friends daniel strong he's got the ultimate   vba course an incredible course from beginning 
to end i don't teach basic vba too much but   if you do want it that is a great course and 
that'll get you up to speed so i'll include the   link down below excel for freelancers followers 
get at least 20 discounts so make sure you jump on   that great so we have this let's go into the item 
macros now what i've got is several modules here   we're going to focus on the items that's the 
first one i want to do i want to dimension   some variables item row the item column last row 
last results row the result row those are all log   variables we're going to be viewing pictures so we 
need a picture folder and we need a picture path   and we need the item picture as a file that way 
because we're going to have to browse for that   item picture okay the first macro that we're going 
to go over is the item new that is the macro that   runs every time we click this button all we're 
going to do is clear out the associated cells   and of course we have a few hidden columns here 
and that's going to help us for the admin so i'm   going to unhide these it's going to display column 
a and b now the item id right here this is the   selected item so if i type in an item here i want 
to show the item id associated with that i also   want to know what row it's on i've got a named 
range called item id if we go under the formulas   and the name manager we take a look at item id we 
have a dynamic named range based on all those item   ids so you can see the last one's 30.

Okay so 
that's item i also have another one called item   name here and i have another one called item 
sorted right remember we had that sorted list   i've got a named range based on that sorted 
list so that we want to use because it is that   sorted list that i want users to select from so i 
want both lists i want item name which is unsorted   which is part of our original data here and i want 
this sorted list which is vba is going to take   care of and sort it for us automatically and show 
it up here so we got two named ranges for that   okay great so inside that what i want to do is 
i want to associate the item row i want to know   that item number 21 is on row 24. now keep in 
mind that inside our items database our first   one starts on row four right our first one starts 
on row four so i want to know that item number 21   is located on row 24.

So how are we going to 
do that well what we're going to do is we're   going to use the match formula and we're going 
to match whatever's in b3 that's the item id   and i want to know an exact match and i want to 
add 3 because our first one starts on row four   if there's an error we're just going to show empty 
an error would be perhaps if we click a new item   then we want to make sure show empty so we know 
that there's no item associated i also want to   know the next item id if you've seen my videos 
before we use it almost in every single video   our next item id is going to be based on all 
the item ids we're going to use the max formula   it's going to give us the largest number and then 
we're going to add 1 so in that case would be 31   because we have 30 all together i want to know the 
selected item id when i select an item here i want   to know what id is here notice it flashed very 
very quickly here take a look at then again here   located in b6 we see that 21 flash real quick as 
soon as i know that we have a id associated with   this item i'm going to place that id directly in 
b3 and i also want to know the picture right when   i load up an item i want to know the name of the 
picture that's associated and this one is called   here let's say we expand this column it's 
called don frisco's coffee png that is the   same name that's going to come so when i lo add a 
new picture that name is going to be changed right   here great so also what i want to know is i want 
to know understand the minimum quantity the sales   price and the purchase price so we have all that 
so let's get so inside our new all we're going   to do is just simply clear out all the associated 
fields i also want to make sure to clear out the   selected id located in b3 so that's what we're 
going to do here b3 and then we're just going   to clear out the remaining of the cells clearing 
those out and also if there's a picture associated   this picture is always going to have the name of 
item pick item pick here i want to make sure that   we automatically clear that out i'm not showing 
pick folder it's just item pick okay i want to   make sure that item pick is automatically deleted 
there okay so that's very important and if there's   no picture it would create an error so therefore 
we've wrapped it in on air resume next and on   error go to zero then i'm going to select e5 we're 
selecting e5 because that is the first one we want   the user to enter that brand new item so they can 
enter a brand new item here so test item right   we'll give it an item type we can give it a brand 
new item type like inventory and non-inventory we   can give it a test invoice description and we can 
also give it a purchase description test purchase   description and of course work order service 
description service description here we can give   it a minimum quantity when we may place an order 
right i want to maybe have that minimum quantity i   don't want to have any stock under 10 if our stock 
goes under 10 i want to know about it we can set   a sales price here and we can set a purchase 
price here so we can do that automatically   and automatically based on the purchases i'll go 
over this formula a little bit later on i want to   know the quantity on hand and basically in basic 
format is all the items that we've purchased minus   all the items that we have sold and that is going 
to be the quantity on hand i also want to be able   to add a picture if i'm going to add a picture we 
can use this invoice with profit which is a great   training so we can quickly add a picture here the 
picture name i want displayed directly inside here   and then we can simply save and update that and 
that's going to save that we'll have a fade out   message that lets us know that the item was saved 
and now it is saved so that is you saw the macro   just one called item save and update when i click 
that button it saved the item so how are we going   to do that well the first thing what we want to 
do is i want to make sure that the user has put   in an item name and type i do have some required 
fields item name and item type are very important   and required if the user leaves those blank we'll 
let the user know to make sure to add those before   saving and we're going to exit the sub out i also 
need to determine is it a new item or there's an   existing item right if we go into this item 
we load this we know it's an existing item   if we click a new item then we know how do we know 
that b4 is going to be blank for new items where   b4 is going to have a row associated if it's 
for an existing item so b4 is going to be our   differentiator and we can use that if b4 equals 
empty we know it is a brand new item there are   a few things that we need to do for new items and 
there's one thing we need to do for existing items   so for new items i want to determine the item row 
that is the item row that's going to be the first   available one inside our item database so the next 
row here is going to be 35 right and we also know   that we need to get a new item id for that which 
would be 32 so what we're going to be doing is   we're going to be pulling that item id directly 
from here b5 i'm going to place this directly   inside b3 and i'm going to set that row up that's 
what we're going to be doing for those new items   so the item row is going to be the first available 
row inside the item database and we have a new   database plus one that's going to give us our new 
database row and our item id we're going to take   whatever's in b5 this is that located inside here 
that's going to set our next item id i'm going to   place that in b3 right here i also want to take 
that item id and i want to place it directly   inside column a so i'll place it right here just 
like you saw we put in this 31 i want to make sure   that we're going to place it also in column a so 
they get placed there so 32 would get placed here   so that's it for our new for an existing item 
all we need to do is extract the item row from b4   everything else is going to be the same and now 
we're going to use data mapping now if you haven't   seen data mapping all we're going to be doing 
is simply we want to map this column item name   or item type or sales description with a specific 
cell inside our item screen here so if we notice   this item name is located in e5 and we notice our 
item type is located in h5 i want to map those   cells directly with the associated columns so we 
see that item name is e5 and we see the item type   is h5 so these are maps all i need to do then is 
loop from two because a is already in there from 2   all the way to the last column and that last 
column is column 10 right i believe column 10   here so we can loop it up all the way column 10 to 
which is this case we're going to loop it all the   way to column j right b7 of course is the mapped 
one that's our picture our picture is located   right here inside b7 here so we're going to map 
it all and we can do that with a for next loop for   the item column equals two column one has already 
been done here right already been done here two   to ten and what we're gonna do is we're gonna 
update that so i'm gonna take that item database   that specific item row or that item column the 
value there is simply gonna be equal what is ever   in the range where is that raised located it's 
located in that first row of the items database   we're going to save and update that so we're going 
to just loop through that taking basically any   data that's here here here or here and placing it 
in the associated row inside our table here here   here so just bring creating that and our updating 
that automatically inside that okay once we have   that that's it all we're going to do is i want to 
run a macro that's automatically going to resort   those names or add that name that macro we're 
going to cover next and i want to run a fade out   message this simple fade out message is just going 
to show this fade out message so it automatically   goes letting the user know that the item has 
been saved and fading it out so for as far as the   sorting names list right if i want to sort that 
that's very very important right so if i change   this from don francisco's coffee to let's say 
fred francisco's because i love fred's a great guy   fred francisco's coffee maybe fred bottom out 
right and i want to save and update that name   i want to make sure that fred is automatically 
if i type in f right i want to make sure that   fred is automatically in here how do we know that 
how do we know it's going to be automatically in   that updated list well we can simply take a look 
inside our drop down list here and scroll down   here and we see that we take a look inside fred 
francisco's coffee notice that it's automatically   been automatically updated right so if i type 
that in here it's automatically if i type it in   here fred francisco's coffee it's going to show up 
and no longer don is no longer available right so   it automatically updated in that and if we 
take a look inside our items database here   we see that fred francisco's coffee has now 
been updated and it's alphabetically sorted   that is going to be for the next macro we're going 
to cover it's called updated sorted names and it   runs every time we save or delete a particular 
item so what we're going to do is we're going   to determine the last row focusing on the items 
database we're going to determine the last row   and i want to clear any existing so whatever 
existing all the way from n3 and down   i want to delete everything there so we're going 
to clear the contents out using this line of code   and then what we want to do is we want to bring 
over all of the names so i'm just simply going to   take all the names located right here between from 
b4 all the way down to the last one so b34 and i'm   just simply going to copy them over right here 
inside column n once they've been placed that we   can do that right here so n and the last row minus 
1 equals b in the last row why are we minusing 1   why is there a differentiator on that because our 
first one starts on three while our first one here   starts on four so we need to differentiate 
between that okay great so then what we're   going to do is we're going to run a sort so first 
thing we're going to do is clear any store fields   and then what we're going to do is we're going 
to set a specific key now that key is going to   be based on the first value which is m3 i want a 
sending alphabetical a to z sort normal and then   we're just going to set that range make sure you 
reset the items database here and here resetting   that sheet name because we're inside another with 
sort we're no longer inside here so we're going   to set that range to end in the last row and 
then we're just going to apply that filter and   that automatically applies the filter and sorts it 
alphabetically the named range automatically takes   care of it remember we have here inside our items 
we have a sorted named range it is that same names   that we're using inside the data validation all 
right so we see when we click data data validation   here inside the advanced here we're going to see 
that we have item name sorted so it is this item   named range that we're using inside that drop down 
list so that's the one we want to select okay very   good so now we've covered that and i want to want 
to show this fade out message this is relatively   simple we use this throughout the application 
i'm going to take a particular shape that shape   is called item saved messages this shape if we 
select on all the shapes and we see all of our   shape we see that item saved message is hidden 
if i were to make it visible we see that we have   a shape called item saved message it is this shape 
that's going to be slowly faded out to letting the   user know all we're going to be doing is running a 
loop from 1 to 100 and then slowly increasing that   transparency as we increase it it becomes faded 
out and then eventually we're simply going to hide   it using visible false that's it that's all we 
have to do to save message great so now we're also   able to as you saw we're able to add a new picture 
simply browsing for a new picture just like that   that is going to be a i don't think i like that 
clears it probably should clear out the existing   picture fred francisco so what we want to do 
is use add a picture this particular button   has been assigned to the macro so if we assign 
a macro we see that is the browse item pick   this is the macro that we're going to go over 
right now first of all again as you just saw we   want to delete any associated picture when we're 
browsing for a new one so for item pick every   time we create a brand new picture inside items 
it's always given the same name called item pick   and here we're simply going to delete it if it 
doesn't exist it could create an error so we're   wrapping it around on air zoom next and on air go 
to zero i'm setting that picture folder remember   inside our admin we created a name reach called 
item picture folder just to refresh your memory   this particular one is called item picture folder 
here so this named range we're going to be using   we can use that inside brackets inside the 
code right here so and i also want to add a   backslash to that right so the picture folder 
we're setting that up and i want to make sure   if the picture folder equals just the backslash or 
if there is an incorrect path right then let the   user know to please set picture for the items and 
how is that helpful well it's helpful if i decide   let's say we have an incorrect path let's 
add an s to this and i try to browse for that   we go back into the item screen and i decide to 
add a picture i want to make sure that there's   an incorrect path please select a folder right so 
it's going to allow us to select that folder so we   can quickly browse for that folder so we can set 
the correct picture right but of course we have it   i'm just going to make sure that so that's what 
we have when we have an incorrect path it helps   us get to the correct path here but once we do 
have a correct path it'll go directly to that path   so this is what we'll do so what it's going to do 
is you saw another macro please select a folder   from the pictures browse for picture folder this 
particular macro is located in our application   macros here and all we're going to be doing is 
just browse for the picture folder all it does   is browse picture file log browse for the picture 
folder that's in our application we'll go back   over a few macros there as well so back inside 
the item macro so we're going to run that once   we have it we're going to check the picture 
folder equals the picture folder we're going   to run another check if the picture folder just 
only equals a bad class means they haven't added   anything or it's an incorrect path we're going to 
exit the sub so we're giving them another chance   and if it's still incorrect we're just going to 
exit the sub out assuming that it is correct we're   going to set that full pathway right i want to set 
the item picture as the application file dialog   file picker we're giving the user the ability to 
pick a specific file so we're going to use file   picker and then we can work with it we're going 
to give it a title called please select an item   picture users we want them to use pictures not 
any particular file so we want to give it some   filters we're going to add a filter we're going 
to give it a name called picture files we're going   to let the user select from jpg png gif jpeg or 
gif okay these are kind of the same thing so and   we're going to give them one so we do not want 
them to select multiple just one single picture   so allow multi-select equals false if for some 
reason shows uh anything else other than negative   ones that means they haven't selected anything 
we're going to go to no selection which is going   to skip down here if they have selected it what i 
want to do is i want to see if i want to place any   picture they have i want to place it in a specific 
folder i want to place all the pictures wherever   they browse for it inside this particular folder 
right because that way i know where to look for it   but what if it's already in that folder if it's 
already in that folder there's nothing to do so   i want to check is it already in the folder that 
means is the folder that is the full path they've   selected here that's where they have browsed is 
it exactly the same as where we want to put it   if it is then go to skip we don't need to skip 
copy it in so we're going to skip the copy and   go down here but what if it's not what if it's not 
the same well what i want to do is i want to check   to see if there is another one with that same name 
if it is we're going to delete it otherwise we're   going to simply going to copy the look picture 
inside the existing folder so we're going to   copy it from its current location selected 
items into its new location picture folder   and the directory of the selected items this right 
here the directory selected is simply the name of   the picture with the extension so we're taking 
the folder with the backslash we're adding onto   the string the name with the extension and that 
is the full file path so these together equal   the full file path of the picture we're simply 
copying it from its existing location into its   new location and giving it a very specific name 
and that's it all we need to use is your file copy   then what i want to do is i want to set the name 
remember that name is located directly in b7 but   i only want the name remember the full name along 
with the extension we're going to use directory   for that directory of the select items it's going 
to place that file name directly in b7 then we're   going to run a macro called item show picture 
right so we're going to have that that's going to   come up right now but there's another macro called 
clear picture if i want to clear the picture i can   do that right let's let's load in here let's 
load in that picture just all we need to do is   load in fred francisco's okay loading that picture 
clearing that picture is going to allow us to do   that we know the picture name so all we need to 
do is just delete the item picture if it doesn't   exist right we certainly don't want an error to 
come up if they clear click clear the picture but   there's no picture there we don't want an error 
to come up so that way we've wrapped it on there   and i also want to clear any files inside b7 okay 
but what i want to also want to show the picture   if i know that there's a specific path and i 
want to show that picture how are we going to   do that well the best way to do that is i want to 
place it directly in the center i want to size it   accordingly and i want to make sure it's in the 
center of column m and i also want to make sure   it's in the center between let's say 4 and 9 right 
or 4 and 10 something like that right i want to   make sure it is centered so we're going to display 
the pictures the first thing what i'm going to do   is i'm going to remove the picture of his existing 
right if there's an existing picture there i want   to remove it next up what i want to do is i want 
to check to make sure we have an accurate picture   folder just like we did before so we're simply 
going to run through the same exact checks that   we did before checking to make sure if it's not 
letting the user know browsing for the picture   checking one more time if it's still inaccurate 
we're going to exit the sub out setting that   picture path setting it into file that picture 
path is simply going to be the picture folder   combined with whatever's in b7 and remember b7 
is the name and the extension so this is the full   file picture path then all we're going to be doing 
i want to make another check to make sure that it   is accurate if the directory of the picture path 
vb directory doesn't equal empty then we can go   ahead and insert it right if it does equal empty 
there's nothing we can do we're going to insert   the pictures so we've already on the item sheet 
we're already here so dot items.pictures.insert   we're inserting that picture path and we're giving 
it a very specific name that name is always going   to be item pick that way it makes it very easy 
to delete then what i want to do is i want to   work with it so with shapes item picture we're 
going to lock that aspect ratio so that way the   uh doesn't get contorted when we resign president 
i want that aspect ratio to be maintained so when   we resize it it doesn't change horizontal 
or vertical too much what i want to do is   i want to check if the width is greater than the 
height i'm going to set the maximum width as 90   however i'm going to set the maximum height as 

That's going to set the width and the height   i want to also set the left position it's going to 
be based on m4 right m4 is our original cell and i   want to base it this is going to be our upper left 
location and our top location so but i also want   to add to that because i want to center it so how 
do we center it well what i'm going to do is i'm   going to take the entire width of m column right 
here and i'm going to subtract the width of the   picture the difference of that i'm going to divide 
by 2 what that's going to do is going to put   equal distance on both the left and the right of 
the picture it's going to center horizontally i   also want to center it vertically we're going to 
do the same thing but this time i'm going to take   rows 4 through 10 and i'm going to determine the 
height of those rows i'm going to subtract the   height of the picture then what i'm going to do is 
going to take that difference and divide it by 2.   that's going to center the picture vertically 
then i'm just going to make sure it's visible   it should automatically be visible automatically 
when i insert it here it should be visible just   to make sure we're centering here okay that's it 
that's all we have to do now you notice another   macro remember all when we did here when i decide 
that i'm going to injure coffee here so let's pick   another let's pick a espresso machine espresso 
coffee machine i want to load that machine here   i can download that picture just a little bit 
sometimes sometimes it does that's a little   bit but it usually does it once and then it's fast 
very fast right so if i do here fred francisco and   then i go back in here see it's fast again okay so 
what i want to do here is i want to make sure that   we have a correct item id now how do we know that 
well we can use a formula for that if there's a   value here a text here what i want to do is i want 
to check so i'm going to run an index because i   want the item id that's associated with whatever 
the user puts in so how are we going to do that   well what i'm going to do is use an index match 
formula i'm going to index the item id i'm going   to use a match that match is going to be based on 
whatever's in e3 and i'm going to based on item   name and zero i want a column and that means what 
we're simply going to be doing is extracting the   item id based on whatever name that enters here 
notice it's blank and if it's blank there's an   error and if there's an error it's going to be 
blank which is fine that's exactly what we want   however if we decide to load it up right 
we're going to automatically notice so if i   pause the code here and then i go ahead 
and i enter the another comment here   enter that you're going to see that of 
course it's paused which is what we want   and now we see here before it gets cleared 
out we see that the selected item id is 22.   so what i want to do now is i know that's accurate 
because b6 contains a value it's not an error   i know that the user has selected a correct item 
name i'm going to take this item id i'm going to   move it directly over to b3 once we're done with 
that we can clear this out and once we clear   what's out in e to three it's automatically going 
to clear out we no longer need this so that's all   we're going to be doing so notice e3 gets cleared 
out in the next line of code as we run this   you see e3 gets cleared out we're going to bring 
that id directly inside b3 that will generate the   item row and then we're going to be able to load 
that item also using data mapping if there's an   existing item picture we're going to delete it of 
course we have to clear the contents of all the   cells associated with that and also i want to make 
sure that b4 contains a value b4 is very critical   because that is the row of the associated id if it 
is blank then we have an incorrect one we need to   let the user know please make sure to select a 
correct item i'm going to turn off application   screen updating making it false that's going to 
help us move things along faster make the code   faster but of course we have to turn it on before 
the macro ends without any exit subs we're going   to set that item row based on b4 and then we're 
going to simply run the reverse data mapping   as we did before but this time in reverse this 
time we're going to pull information directly   from the item database on the item row on the 
item column we're going to place it directly   inside the range and that range inside our item 
sheet is located directly here on row one in the   item column as we loop through that right so it's 
going to take information from the items database   and it's going to place this picture in b7 it's 
going to place this amount the cost inside k9 and   so on and so forth as it loops from two to ten so 
it's going to place all those values inside there   accordingly okay very very good so we've 
got that so that's all we need to do   and then if b7 value doesn't equal empty meaning 
we do have another picture we're going to run the   macro show item picture and that's the macro that 
i already went over up here okay great so that's   it and then of course before ending we're going to 
turn application screen updating on background to   true item delete relatively simple when i want 
to delete an item it's very easy all i need to   do is just do that i want an option to come up if 
i delete an item i want to let the user know are   you sure you want to delete this item that's the 
first thing are you sure you want to delete this   item giving the user vb yes or no giving it a name 
called delete item if it's no we're just going to   exit the sub as i just did then what i want to 
know if it has been saved or not why is that   important because if i create a brand new item and 
i enter some name in here right and some type in   here and i decide i want to delete that there's no 
row associated with that so when i delete an item   all we're going to be doing is just clearing the 
contents there's no row associated with that so we   can skip deleting any row so we're going to go all 
the way to not say which is going to go down here   it's going to delete any row i mean it's not going 
to delete any row because there's no database row   associated with that because it has never been 
saved however if it has been saved and b4 does   contain that row then i want to put that into 
a variable called item row and then inside the   items database item row and the item row entire 
row delete lastly for all that whether it's been   saved or not we're going to run the mac or item 
new that's going to clear out all the fields   then we're going to again we're going to update 
the list if i want to update the list i want   to make sure to run the macro that updates that 
list and as you can see inside the database here   the items database here if we take a look inside 
that sorted list here let's take a look inside the   item database we see that sort of list the last 
one that i added test item right that's already   included however if i go into the items and i 
decide i'm going to delete this item and i add it   in and then i click delete and i do want to delete 
it i want to make sure it's cleared out right and   i also want to make sure that inside the items 
database that it is no longer here so you see the   last one is no longer here and that's exactly what 
i want to happen and so that's it that's all we   need to do for items that covers everything on 
items okay very good next up i want to go inside   the transactions that we've got some transactions 
and that's going to be based on this transaction   screen we can get to it directly from the home 
or we can get to it from here okay so this   transactions is almost the same relatively very 
similar except this one's got a really really cool   load and filter this time in this case 
we're not selecting from a transaction   but we have different available options that we 
can select from the existing transactions here   because we have this really cool filter it can 
allow us to search for transactions based on date   name account or amount so that means if i want 
to search based on a specific date and i only   want to know those located on six seven i can 
do just that and it's going to load only those   transactions based on a certain date or perhaps 
maybe i want a certain name so maybe i only want   fred as a name it's going to search only those 
transactions let's clear that out i don't have   any on fred but how about david david sure david 
will work for us david i only want to show david's   transactions great so we've got a search filter 
but let's go over some of the required fields   i've got different required fields i mean this is 
very important i've got a transaction date i've   got an expense an income expense i want to know 
what type i want to name right name and i want   the account that's very important and the amount 
so those are all required i've got five different   required fields if i do a new transaction and i 
don't enter all those fields and i try to save   it i need to make sure that we know please make 
sure to add transaction date type name account   and amount before saving this transaction so we 
had forgotten account and amount so i want to make   sure that we put those in so that's very important 
so but i need to know how many require fields now   inside vba we don't want to check if this is empty 
if this is empty it's just it's a it's a lot more   work but what if i simply count all those fields 
of those five what if i count all the ones that   contain a value and if it's less than that 
so we've got five different fields so if we   use something called count a and we look at all 
those and we see that five have five of those five   do contain values using counting we know that 
they can go ahead and save it because all those   fields are required memo is not required so this 
is a great way to quickly determine if all the   chronic fields have been filled out or not so if 
b2 is less than that then we know we've got the   transaction id that's the specific as we load a 
transaction that id will change remember inside   our transaction database they have transaction ids 
date so just like with that we also want to make   sure that we have the transaction row what is 
the row that's associated with that and we know   that transaction number two is located on row five 
inside our transaction database transaction number   two is located on row five again our first ones 
start on row four just like with items so we wanna   make sure that we are going to use the match and 
we're adding three if there's an error we're going   to show blank right so if it's a new transaction 
of course it would be blank all right so i also   want to know the next transaction id using the max 
just like we did on items so no different there   were simply max all the transaction ids of course 
i've got a named range based on the transaction   ids right named range dynamic transactions so the 
next one would be i've got one for transaction   type here and i've got one for transaction date 
that's going to help us out so i also want to   know the selected transaction row we've got 
conditional formatting here i want to know the   row that's associated let's take a look at some of 
the conditional formatting go into this and we're   going to manage those rules and we take a look 
we've got three different conditional formattings   one is based on the selected row of b6 of course 
we're going to use a white font and darker color   then i'm going to use i want to call the odd rows 
but only if they contain a value and column d   and i want to color the even rows but only if they 
contain a value and column d as well but only for   even rows so we've given that a little bit look so 
that way odd rows have a light blue and even rows   have white for the odd rows and then for the 
even rows we're going to show that bluer okay   so we've got that so that's going to be 
conditional formatting so that way on our   selection change event when i make a selection 
change all i need to do is place that row and   place it directly in b6 that will automatically 
fire that conditional formatting and what i'm   going to do is i've also got a hidden column 
here some hidden values here and i've got the   transaction id located here just as we did with 
that so if we go into home again and we see we've   got a custom filter if i change this to general 
you're going to see that all those appear here if   we undo that and remember just as we did before 
we given that a custom format and to hide those   how do we do that we go into marsh number formats 
and give it those three semicolons that's going to   hide it now why is it important to have those 
transaction ids here because when i select on   a row whatever transaction id is located in column 
c i'm going to place that transaction id inside b3   and what that's going to do is automatically 
calculate that row when i know the row of the   transaction i can automatically load that data 
here and again using data mapping just as we did   before j4 m4 j6 j8 those are all of the cells 
associated with those transactions right here   j4 here m4 here and then j6 so they're associated 
with that that's going to make coding a lot easier   i also want to know the selected name row so what 
is that selected name row that is important why   is that important because i want to know what 
row associated so if i change the name on here   remember we have names is all i want to know to 
see if it's a new name if i decide to type in   freder's fred freddy right and i want a new name 
and that name doesn't exist i want to be able   to add it right so if i how do i if i save that 
transaction i want to make sure that we're saving   it or maybe i want to add it here so i can quickly 
add it as a vendor here and if i save it this is   automatically now going to be contained inside 
the list quickly and it's associated with this   v7 so basically if i have a match based on name 
names this is a named manager let's go into that   so we can look at that into the formula i know 
it's kind of a strange name called name names   name is our database right here and names names as 
our database here and names is located here so we   have a list of names here so what i know is if it 
doesn't exist on that then it is a brand new one   notice we just add fretters fred freddy here so 
we've added that here because we know that if it's   blank here we know that when we edit it and i'll 
go over this but now when we click it we can add   update so if i want to update an address here 
i can do just that very very easily with this   and of course we can save and i can save it 
and kind of change it as customers so now if we   go back into the names database we see that it's 
already been added okay so i'll be going over that   like i said this is a massive application right 
i started this monday and it is now friday so   working hard for you i hope you appreciate it 
don't forget to smash that like button share this   video we appreciate you don't forget to subscribe 
if you haven't already comment below every little   bit helps makes maintaining these videos always 
free and of course many ways to support us using   the links down in the description so continuing 
on with our transactions so i wanted to explain   to you we'll be going over how to do this in a 
moment but i wanted to make sure you understand   why we have a named robe because this is the row 
that's associated with this name as soon as we add   something that doesn't exist this is blank that 
way we know how to automatically based on this   if it's blank we know to add a new one however 
if it is not blank we know that we can simply   add it we can simply select it and then edit the 
existing one because we know the order that it's   in great so i also want to know the next name 
id just as we did before inside and of course we   can do the exactly the same right just this same 
inside the orders the same thing here right if we   add a new one here john jacob smith or jay comes 
smith okay so if we add a new one i wanted this   name doesn't matter okay actually i don't want 
to do that actually i'm going to go into data   validation just real quickly and i want to make 
sure that users can enter a new one we don't want   an error so we want the user to add something 
right so if we click here i want to make sure   that they can simply add it and save it here okay 
good that's exactly what i want all right good so   we see that we're going to do the same thing so 
we have the same ability both in our orders and   our transactions here okay so we also want to know 
the next name id this is simply the max of all the   name ids right name ids are located right in our 
names database these are all the names ids i want   to know the next one which is 26 using the max 
formula so that way when we create a new one we   know exactly what id assigned it to and we know if 
it's new or not because this is going to be blank   if it's not we know so we've got we understand 
what we've done all this let's go ahead and get   into some of the particular macros on-site our 
transactions and of course we're going to do same   thing transaction column some variables here 
that we'll be going over answering we want to   load the list the first thing of course when i 
create a brand new one i want to make sure that   we're loading this list of transactions and it's 
going to be based on some filters we know what   database it's coming from we know it's coming 
from the transaction database and we have to   have some dynamic search criteria it could be name 
it could be several different ones right so when   we create that right i want to know what are we 
basing it on right if i create an account right i   only want to know maybe lease expense so when 
i create a name range i only want to know those   transactions associated with lease expense so 
that's going to be some criteria but it's dynamic   based on the name and based on the value right so 
we take a look inside the transaction database we   have some criteria but it again it is dynamic 
as you saw it's going to be based on whatever's   in transactions e5 and then also what i want to 
know is i want to know the least expenses but i   don't want to know the what if they just entered 
least i don't want always an exact match i still   wanted to come up if they enter a personal match 
so if i type in just lease alone right i want to   make sure that it comes up so what we want to 
do is i want to match any possibility if we   add in those asterisks before and after as you 
saw in that that is going to be the wild card   that means any count containing the word lease 
is automatically going to come up if we just add   something like e right you're going to see a lot 
of them come up anything that contains the word e   so inside our transaction database we want to 
surround it with that however not for every type   of search right because inside our transactions if 
i enter an amount if i let's say i want an amount   i want an exact amount or maybe a partial match 
or maybe i only want to know those great less   than 100 so if i want to type in less than 100 i 
only want to know those transactions less than 100   so we can use that so for this particular or maybe 
on a date maybe we want to set a specific date so   if we have a date here maybe we only want to know 
less than 6 let's say 6 2 22 now or 2 0 2 2.

Okay   so we only want to know those transactions less so 
we could for these types for both date and amount   we certainly don't want to use the asterisk 
right i want to use exactly what's in there so   we have to make a little bit of a change inside 
our formula and let's take a look inside that   so our transaction so we've got a little bit 
of a formula here first of all i want to know   if it's enter search if the word is intersect then 
just put in does not equal right enter search is   just the default so we certainly don't want to 
search for enter search otherwise what if it's   name or account if it's name or account if the 
user has searched by name or by account only then   are we going to use the asterisk based on whatever 
they've entered which is f5 we're going to add the   asterisk before and after it that's going to be 
automatically so this wild card so that means any   search containing those will be up however what 
if it is amount or what if it is date in that   case we're simply going to put in exactly what the 
user has entered transactions right so that means   exactly as so here it's less than 62 but what if 
i only wanted a 6-1 well i can do that as well   inside transactions if i want to search only on a 
specific day i just do 6-1 and it's going to enter   those transactions only so you see in date and 
amount we're going to enter exactly what users   has entered otherwise if it is account or name 
we're going to use the asterisk and we can clear   the filter here i'm just going to clear the filter 
okay so now that we understand how the criteria   works inside our transactions we want those search 
results to come here and i want the transaction id   the date the name account amount all that 
information is going to be coming directly over   insider however of course that transaction id is 
hidden using the three semicolons so that's what   we're going to do we're going to run that advanced 
filter and that's all we're going to do so if we   clear the filter right loading the list is here 
let's go over that clearing clearance filters next   so all we're going to do is certainly clear all 
the existing transactions so when we run this   i want to make sure that from c7 all the way 
through g and down everything gets cleared out   that's the first thing we want to do so inside 
the transactions b6 i also want to know why is   b6 important b6 is also our selected transaction 
role i want to clear that out too that's going   to remove that conditional formatting so i want 
to clear that out okay and also i want to clear   out all the associated data here clearing all 
existing transactions then we're going to focus   on the transaction database determining the last 
row if it's less than four that means we have   no data we're going to exit the sub up we're 
going to run our advanced filter our advanced   filter is going to be coming directly inside our 
transaction we're going to come all the way to g   from a3 to g that criteria is going to be l2 
through l3 and those results are going to come o3   through s and that's exactly what we have inside 
our advanced filter a33g our results l2 through   l3 and our our results o2 through s2 we're going 
to determine the last row of our results based on   column o if it's less than 3 that means we have 
no data right if it's less than 3 here we know   we've got no data if we do have data i'm going to 
bring all of that data over so the transaction c7   through the last row plus 4 and that's important 
because our data starts on row 3 here while inside   our transactions our row starts on row seven so 
we need to compensate for that by adding four and   that's just what we do right here and then we're 
going to bring i'll bring those transactions over   and then of course if transaction what i also 
want to do when i clear that filter notice that   that row got removed right we removed removed but 
what i want to do is when we run that filter i   want to look for that transaction id here and if 
it's found remember that's hidden so you can see   up here in the formula bar it's 1 right so what 
i want to do is i want to take this transaction   id and i want to look to see if that transaction 
id is here if it's found whatever row it's found   on notice it's found on 7 here i want to place 
that 7 right here so if i clear that filter   you see that that 7 got placed right here that 
7 got placed here because it was found on row 7.   that's going to trigger the conditional 
formatting so the user knows automatically   what transaction has already been loaded right so 
if i select here and i clear the filter out here i   want that exact row to be selected again remember 
when we clear the filter out this gets deleted but   i still want to know what selected row so once we 
locate we know the transaction id is here once we   locate the transaction id here has been loaded 
so we're going to check to make sure that that   row gets selected again simply by adding the row 
in here and that's what we're going to do here   however if there's no transaction id in b3 then 
we can exit the sub out so we're going to simply   look if b3 or b4 before meaning there's no row 
associated with that we're going to exit the sub   now what i want to do is i want to place directly 
inside b6 remember b6 is where it's going to go   i want to look for this this transaction id and 
i'm going to look in here if it's found whatever   row it's found on place it directly inside b6 
that's what we're going to do with this line of   code b6 is equal to the transaction c7 through 
the network we're looking for whatever's in b3   we're going to extract the row if it's found 
we're going to place it in b6 if it's not it   could create an error so make sure you wrap it in 
on a resume next on or go to zero okay and then   if b6 does not equal empty then what i want to do 
is i just want to select and that makes sure that   any once we select d it's going to automatically 
load in that transaction it's going to make it   load it in and that way any changes that have 
been taken place will automatically load it in   okay great so that's all we can do so that that's 
important because if we've made an update right if   i change this to test memo 7 right and i make that 
change and i update that list and i save it right   and i want to make sure please add oh we haven't 
had a date okay let's save it i like that adding   davis we've added that now he's been added and 
so now we see that test memo seven we select here   that test memo seven that memo's automatically 
been updated okay great so clearing the filter   all we need to do is just click enter search 
right as soon as i make a change here it's going   to trigger that how do we know that well let's 
take a look inside the transaction screen here   transactions here here we take a look inside if we 
make a change a worksheet change to f5 and we want   to make sure that it's nothing then we're going 
to automatically load this list so basically any   change that we make to f5 we're going to run that 
macro that we just went over load list so that   macro gets run automatically here and likewise if 
we make a selection change remember we're making   selection change here we want something to happen 
i want to take whatever's in c and the selected   row and to place it directly inside b3 and then 
load it up so we're going to make sure if count   large user selects more than one cell exit the sub 
if the user makes a selection change from d7 and   we want to make sure that c contains a value c of 
course is that transaction id we want to make sure   then what i'm going to do is i'm going to take 
b6 and i'm going to place it directly i want   to know that row remember that selected row must 
go directly in b6 and the second thing i'm going   to do is i'm going to take whatever values in c 
i'm going to place it directly inside b3 so b3 is   simply going to equal c in the target row set that 
transaction id then we're going to run the macro   to load the transactions so that's all we have to 
do clearing the filter as soon as we enter search   near any change to f5 is automatically going to 
trigger this macro to run so we don't need to run   it again when we add a new transaction all we're 
doing is clearing a bunch of associated cells   setting j4 setting that default date new i want 
to make sure the current date june 10th right j   is going to take on that current date and i want 
to make sure that j6 select there's one more thing   i want to add remember we were discussing having 
a default account remember we discussed that early   on the training this account we want to set the 
default j8 what do we want to equal we want to   equal whatever is located here in admin g and we 
could just call this let's give it a name which   i like called default transaction account okay 
makes it easier oops spelled that wrong default   transaction okay let's set that up and notice when 
you create that we want to create it's better to   create redo it through the name manager so default 
transaction account edit that and just update that   default transaction account that's what i'd like 
to call it i'm going to copy that and click ok   and we would name exist because we added it 
twice so that's fine all right no problem   do they're both here we could just delete this one 
here good okay i wish it would replace it you know   on a single cell but it just adds to it instead of 
replacing it okay great so what we want to do is   dot range and what is that called so what is 
the cell located we want to place it directly   inside a particular cell inside that let's go 
and do the transactions again here and i want   to place it directly inside j8 so the transaction 
j8 dot range j8 for the default is simply equal   to we can use the brackets here to make it a lot 
quicker pasting that default transaction that's   going to set the default trends i set default 
transaction account that way for new transactions   it's automatically going to set that account if 
you have that available i mean if you've added   it so now when we go back in here and we just 
add in our default creating a new transaction   it's going to add in that default account right 
here okay good i like that so we've now added in   that default account and we're going to select j6 
and that's going to let the user automatically add   in anything they want here inside j6 continuing on 
with our code now what we're going to do is we're   going to save and update the transaction we'll 
move a little quicker because these are very very   simple as we mentioned before b2 is the number of 
required fields if it's not 5 we know that they   have not added enough required fields we're going 
to let them know if b8 equals empty i want to   let the user know to please save this transaction 
before remember how do we know that well b8 is our   thing so if we added either none right so if if 
ba contains a value i know it's an existing name   if it is not if i put in another name i want 
to there's if b it is empty i need to know let   the user to add that name before so if b8 is 
empty please add and save whatever's in j6 so   we're going to try to save it please make sure 
to add transaction date okay we didn't add the   amount here so let's add that first then click 
save and we see please add sdss before saving   this transaction right so we can add it we can 
cancel that so this is going to let us know and   add that name first which i really like okay so it 
says here so we know because that name has never   been saved what we're going to be doing is we're 
going to let the user know for this message box   and we're going to run a macro called transaction 
add edit name we'll be going over this macro in   a little bit just a little bit transaction add 
edit name and that's going to automatically pop   up that user form so b if b8 still equals empty 
we're going to even after they've run this macro   to add it if it's still empty we're going to exit 
this up okay if b4 equals empty we know it's a new   so now we need to differentiate between in a brand 
new transaction meaning b4 is empty or an existing   transaction where b4 contains a row so we need to 
differentiate that if it is new all we need to do   is set a new transaction row based on the first 
available row setting that trans transaction id   placing it from b5 into b3 and setting that 
new transaction id in column eight if it's an   existing transaction we're just going to extract 
that row directly from b4 then we're going to run   data mapping just as we did before simply taking 
all of the information from the ranges there   that we've mapped out and placing that inside the 
database the transaction database then we're going   to simply run the mac or that macro we've already 
been able to reload the list and then a fade out   message and then fade out message is simply just 
what we did before and we just click save and it's   going to be a fit of oh mary poppins we've saved 
that before okay there we go saving that okay so   now we see transactions saved here so we've got 
that available here so i like that very much very   good now moving on right that that beta message 
is simply the same thing that we went over before   loading the transaction again we're going to clear 
the contents going to make sure that b4 contains   a value right we've already know as soon as we 
run this that macro to load it's going to run as   soon as long as we have a row located in b4 if it 
does have a row of course we're going to put that   into a variable called transaction row we're then 
going to run use data mapping in the reverse style   simply adding all that data in and then turning 
on application screen updating that is it deleting   the transaction very very similar to items all 
we're going to be doing is making sure has the   transaction been saved before we're going to get 
after the warning message if it has never been   saved because b4 is empty we're just going to skip 
deleting the row but if there is a row we're going   to put that into a variable then what i want 
to do is just actually we don't need this here   we can just delete that that's not a transaction 
row delete the entire row delete that transaction   row we don't need it twice there that's not 
going to be helpful okay so the transaction   deleting the transaction row and then we're going 
to add the new transaction and reload the list   that is it all right now this is one remember we 
had another one called transaction adder edit name   this is the macro that's automatically been tied 
to this right so i've got a brand new user form   this user form is located right here called add 
edit names this is a user form it's very very cool   because it allows us to one have a drop down list 
we've got customer vendor employee name and we've   got different fields associated with that this is 
called field one field two three and it's gonna go   all the way to field 7.

They're given specific 
names this one is called name type this one is   called status right so we've got different field 
names so it's going to allow us to add or edit   names when i run this macro that's going to 
allow us to edit names what i want to do is   i want to determine if it's a new or an existing 
so transaction add our edit name we're going to   define the name field as control and the name room 
as long and the name column is long we're going to   be using another data mapping what i want to 
do is i want to clear any fields in the form   first so the first thing with the transactions 
focusing on this screen i want to know is it a new   name or is an existing name that's very important 
right a new name knows harold banks is new while   mary badger david davis is existing being 
b8 so right if i haven't added merry badges   i think i add this through the database 
so i think if you add it in a database   notice that harold banks here has not been added 
so if i decide to edit here we know it is a new   name because b8 is empty right if we look inside 
our names database we don't see that harold banks   is in here anywhere at all so we know it's going 
to be brand new okay so to do that we need to   differentiate b8s that differentiator if it's 
empty it is a new name what i want to do is i   want to take that field one from our form here the 
value of that whatever is in j6 i want to place   there j6 is inside it is our name transaction 
right here so what that means automatically when   i launch this form i want that name to go directly 
inside field one i'd also want to set it as the   vendor so what i want to do is i want to set 
that default to whatever the vendor name is that   type remember the name type here this is the field 
that we're going to focus on here this is our name   type right up here this first one is called name 
type i want to set that up to vendor right because   normally if we enter a transaction of course 
we can change it but we're just assuming that   they want to enter a vendor and this maybe not a 
customer i also want to set the default to active   right that's status value to active however if 
it isn't existing i just want to set the name   row this would be existing name existing name 
because ba contains a value if it is existing   like mary poppins is here or david davis is here i 
want to make sure that we're going to load all the   information i think this one has addressed as well 
i want to make sure to load all the information   associated with that vendor pulling in all the 
information from the names database so if asia   electric i want to put in their po their city 
state i want to bring all that inside the form   using data mapping so how are we going to do that 
well the first thing what we're going to be doing   is we're going to set that name row whatever's in 
b8 i want to know the database row then what i'm   going to do is run a another loop right from 3 2 9 
why 3 to 9 because we already know that it's name   id we know that it's a customer from 3 here all 
the way to the last 9 okay actually to the email   which is nine here if we take a look inside this 
column equals column we see that that is column   nine okay so i'm going to run it there that's the 
one i want to run and i want to run a loop okay   so this is going to help us out notice that we've 
defined the name field as a control so we're going   to set that name field to equal to add edit this 
is our form name controls field and then the name   column minus two why is this important our column 
starts out of three but our first field name which   is our name is field one do we remember that our 
first the name of this field it's located field   one this is field two so i wanna put that name 
from column three inside field one so to do that   we need to map the data so name column three minus 
two this is one field and field one so our name   gonna come from three so we're gonna loop through 
that so our name field value is equal to the names   based on whatever's in the name row and the name 
column this is our database this is where it's   coming from so this will loop out that saves us 
a lot of code so we can automatically fill in   that form using data mapping and then the last 
two i want to set the name type right that add   or edit name this one the name type based on 
whatever's in column b and i want to set the   status whatsoever in column j so again taking a 
look at this our type is in column b our status   is in column j we're setting those two because 
those are different fields those are drop down   lists so they're slightly different okay great so 
that's all we have to do and then i'm just simply   going to display the form and that way as soon 
as we run that form that has been tied to this   button here when we edit that we see that all the 
data will load automatically and if we decide to   change that or save it we can automatically update 
it so if i decide to save this clicking save   it's automatically going to update it here how do 
we do that well that's the save button so if we   go in here and we click view code we see that we 
have the save button click this is the code that   runs automatically this is the code that runs on 
cancel we have two buttons cancel which is simply   going to hide the form and the other button called 
save button so this is going to save our fields so   again we're going to do name field as a control 
we want the name row and the name columns long   just as we did before i want to make sure 
that they've put in the required fields   if the field one which is the name is empty or 
the type is empty then we're going to make sure   to please add a name and type before saving we're 
going to exit this up those are required so once   we know we've got the required fields then we're 
going to focus on that names database we're going   to check for a new or existing what i want to 
know is if active sheet b8 equals new name well   now why are we using active sheet the reason 
we're using active sheet is because i have this   type of details and programming based 
on two different sheets transactions   and orders let's take a look in here both of them 
use the same cells b8 and b9 right i've used the   same so we can use active sheet selected name 
row is going to be here so we can use this same   additional feature for both the order sheet and 
the transaction sheet so that's why we're going to   use active sheet because either one we're using 
the same detail same cells so if active sheet   b8 is empty we know that there's no row that's 
associated with that name we know it's a new one   i need to find a new row for that that number is 
going to be the first based on the names database   the first available row right then what i want 
to do is i want to take that next id right now   look inside here this is the next id using the max 
formula also inside the order screen same thing b9   is going to have the next one exactly the same 
cells so we can do the same function on either   screen as long as we use active sheet because we 
know if they're on orders and they decide to add   a new name or if they're on transaction it's 
going to be b9 b9 is going to come from that   active sheet so we're going to so we know that a 
our id is going to come from the axe gp9 this is   our unique id otherwise what if it's an existing 
so existing for this existing name all we need   to do is extract the row directly from b8 this is 
our existing name row everything else we're going   to do whether it is a new or an existing name 
we're simply going to make those updates right   so column b is going to take on the type column j 
is going to take on the status then all we need to   run the loop for the remaining fields from three 
to nine just like we did before except in reverse   before we loaded the information into the form now 
we're taking the form details and putting it into   the database we're going to set that name field 
again just like we did before field name control   our first field is field one but our column three 
so three minus two is one field one all the way to   field all right okay so then we're going to simply 
take whatever's in that database names database   the name row the name column is equal to the field 
name value this adds it adds or updates the field   data inside the database okay so that's it that's 
all we have to do then we just simply hide that   form then what i want to do is once we've made a 
change i want to run a macro called names update   and sort remember we have that macro here called 
names up to actually haven't gone over this macro   simply all we're doing here in this macro which 
we will go over in a bit is simply updating those   names we did go over briefly updating the names 
to make sure that we have a macro that's going to   update all names now i want names i want our names 
are separated between customers vendors employees   so i want three lists one with all names one 
with customers one with vendors and one with   employees so there's a macro that we're going to 
go over shortly that's going to do all that hard   work and heavy lifting for us to separate it 
into three different rules why because we need   names customers for invoices i need vendors for 
purchase orders and i need employees to assign to   employees for work orders and other things so our 
orders are of course our employees are assigned   here so i need three different lists right vendors 
for here for invoices we're going to need when   we have invoices we're going to need invoices so 
we need three different lists for that so that's   going to help us so on our names database we need 
to create a macro that's going to do that and as   soon as we make any changes to a name we want 
these lists automatically to resort through a   macro and that macro is located right here called 
update and sort now what i want to do is i want to   differentiate a little bit between remember i said 
we can run this macro whether we're on orders or   transactions so if we're on orders what i want to 
do is i want to set once we've updated if we're   on the orders form whatever changes that we've 
made to value one that name i want to put it in   f5 right so if i've decided i've made a change i 
want to make sure that that change gets reflected   inside f5 what is f5 well that is right here right 
so if i decide i'm going to add a name right and   i want to update that let's say i want to update 
somebody's name let's say john james and i want to   update john james and i want to make give him the 
last name smith right i want to make that update   so when i make that update i want to make sure 
that that change gets reflected directly inside f5   right if i decide to make that additional change 
like here asian electric and i want to take away   that s i want to make sure that that change 
gets reflected directly inside j6 so here we   have the differentiate so if we're on orders we 
certainly need to update f5 which is the name   of the orders otherwise inside the transactions 
on the transaction j6 is going to take on that   updated field that way we update the name inside 
that field as well whether we're on orders or   whether we're on the transaction sheet and that 
is it that's all we need to do as far as saving   this so that's all we need to do relatively easy 
okay very good so inside the transactions macros   we've gone over added edit name we've covered 
all the transactions we've covered all the map   items and we've covered most of the add and edit 
names very good we're making great progress we're   going to focus on the order macros and the order 
sheet macro so that's what we're going to go over   right now mentioned earlier this order sheet has 
the capability to handle four different types of   orders whether it's estimates work orders invoices 
or purchase orders this sheet can handle it all   because there's so many similarities between 
those four different types of orders we might as   well combine them into a single sheet however we 
certainly want to make sure that they look and act   very differently so that users don't get confused 
which type of order they're on and so what we've   done is we've used conditional formatting to color 
them accordingly and so they can have each one has   a different look and feel now when we do change 
the order types we have the order type located   inside b2 so when we change it to a work order b2 
is always going to tell us what that order type   is we've given b2 a very important name variance 
called order type so that we can refer to that   both using formulas inside conditional formatting 
and inside the vba code so we're going to often   refer to this order type here the name range right 
up here you can see this that's very important so   the first thing which you'll obviously recognize 
very quickly is the conditional formatting that's   used to differentiate between the colors of those 
type of forms and it is based on that conditional   formatting on that so let's go ahead and take a 
look inside some of the cells here and we'll look   at some of the conditional formatting that we're 
using so we're going to click on manage rules   selecting some and we see that we have different 
colors based on this so we've got our header row   we've got the second row we've got a header row 
which is row one we've got our second row which is   row two and then we have the remaining rows each 
one of them has individual conditional formatting   and our natural color which is the color is blue 
so invoicing is blue so we don't need to change   anything to blue because that is our original 
color so we're going to make changes for estimates   work orders and purchase orders so each one of 
those is relatively simple if b2 equals admin c5   we can actually change this to the estimate order 
so we can change it i generally like to go with it   so we'll do estimate order so this is the same 
thing the same named range we're going to get   the same output when we apply it so there's going 
to be no difference so if b2 equals estimate order   we're going to give it this yellow color for row 
one in this yellow color just like they are it's   simply a fade out color that we're going to use 
which is the medium yellow to a little bit lighter   yellow so we go into the fill we use the fill 
effects and we see that it is this darker yellow   to the medium yellow okay and we've done simply 
that on the second row so the second row that we   have row two here on our edit row is that medium 
color based on the estimator so when we do the   fill effects we see that we have that medium color 
to that light color yellow okay so only of course   when our b2 equals the estimate order and then 
lastly they're all the remaining cells which is   all the cells all the way around the sesame order 
including the header here we've given it that   light yellow color so this is a single color 
we've formatted this this is that light yellow   that's single color yellow so i've done this 
exact same thing whether we're on admin remember   this can say work order i'll add edit that to 
underscore work order which is the same thing work   right you can use named ranges to make sure you 
got the name range correct work order okay this   is the same one and it's b2 equals work order 
so the greens and then likely the purchase order   all have the purple so we're going to use that and 
click apply and then we have everything there so   that when we change this from to work order we 
get the green from to purchase order we get the   purple and our original color which doesn't have 
any of them this is our original color that blue   so each one of those contains a different color 
now we have some additional formatting as we   notice here income excuse me income and payment 
amount those are visible for both invoices and   for purchase orders however when we select on 
estimates or when we select on work orders we   see that those two fields are hidden so we've used 
conditional formatting to do that so we go into   the home and we can digital formatting we see that 
we have additional ones for order type equals work   order we want to color those green right so we've 
got some additional formatting and we also want to   color we don't want to remove the border so we've 
removed borders for both estimate orders and order   types if either one of those are actually we can 
remove some so let's take a look at this rule here   we format that and we look in the borders and we 
see that the right border has been removed right   so i don't want that right border to show up for 
both uh work orders and estimate orders if the   order type equals estimate order or the order type 
equals work order in that case i want to remove   the right border and we're going to apply that to 
c11 and c12 so we want to remove that right border   and again just likewise in c12 to d12 what do i 
want to do if we edit that rule again if either   one of those are s motor what i want to do is 
i want to remove the top and the bottom borders   right i want to move those and that's on row 12 
so i don't remove those borders so basically we   just don't want to show those on either estimates 
or work orders okay so but otherwise both purchase   orders or invoices can have the income account 
okay so that's pretty much it for the conditional   formatting and we have a little bit more on 
the taxes i've got some additional conditional   formatting down here payments and balance that's 
not going to show up on the work order so when we   click on work orders we see that there's obviously 
a lot more here we're not going to show up and   also estimates here estimates would also not have 
would have a total but we wouldn't want to show   any payments so we want to don't want to show any 
information so when i click here and go into the   conditional format and we see manage rules we got 
some additional rules here so let's take a look at   this row or so this particular rule or order type 
we look at this we see both for work order and   order estimator for both of those what do i want 
to do i want to give it the font here i want to   give it a white font color that white font color 
will hide the payments and the subtotal certainly   for both estimates and work orders we do not 
want to show any type of a payment or any type of   a balance on that so we certainly don't want 
that however on work orders we also want to   add additional work orders we see we don't have 
any total fields prices are total so we don't   want them to show up on work orders and we also 
certainly don't want to show other things on work   orders so we notice we want to hide this entire 
section on work orders so when we look down here   we see the work orders the type work order we 
give it a format we see that the borders the right   border has been hidden and we've given it them 
information the font here is going to be automatic   so let's go ahead and switch to work orders 
so we can see how that applies automatically   on work orders we see that we've hidden everything 
everything's been hidden so we've got a lot more   for work orders hiding up all those borders 
right so going into the home right conditional   formatting and manage those rules and we see 
that we have a lot more in work orders here   on workloads we've got a lot of information here 
all these cells we want to hide what i want to do   i want to hide that font right so we're going to 
format that and we see that the font here is white   we've chosen a font color of white so that well 
we can hide those on work orders because we don't   want to show any total information on work orders 
so we can see how conditional formatting can also   hide both rows both information and both cells 
using the font color and also we can hide the   borders on that for those that we don't want to 
conditional formatting is a great way to help us   differentiate between type orders we also have a 
conditional formatting here we also want the font   we notice that the font size both the font 
and the text are different right we notice   that we now have a formula called order type 
so the order type whichever is located in b2   is automatically going to be an upper case and it 
is automatically going to show that one so when we   click invoice it's automatically going to show 
whatever is located in b2 which is our order type   and we have conditional formatting here 
also to change the color of that so we   have three different conditional formatting 
for purge shorter work order and astromotor   the natural color the inherent original 
color is blue so this what you're seeing now   no conditional formatting applies however when 
we change it to purchase order the font will go   to purple work order green and the estimate order 
this brown or yellow okay so that's how we change   that there and that's going to be really helpful 
as we go on all right let's take a look at some   of the additional data notice when we change this 
to work order we see that a lot of things change   this changes to work order number this changes to 
work order date we can simply use that order type   and the number inside a formula we can simply 
use the employee name here we can simply use the   order type and the date here so that way we can 
automatically customize that so we've got that   inside there so inside the estimate it's going 
to show estimate date estimate number inside   the purchase order we're going to show purchase 
order date purchase order all we need to do is add   in the order type very very helpful we also have 
conditional formatting here for both work orders   and estimates so that we can hide that so that's 
just conditional formatting just as we did before   okay great and we can see that we can use the 
formula to add in the dynamic order name the order   type name here as well and we can have that here 
too another thing that we have here is i've got   here vendor showing on purchase orders however on 
estimates work orders and invoices it's going to   show customer we can use simply formula for that 
if the order type equals purchase order show the   vendor name remember this is the dynamic name 
using vendor name right just in case we forget   remember vendor name this is the vendor name 
whatever use you know right if you want to change   this to purchaser right let's say we change this 
to purchaser right we want to show that we want to   make sure that that gets automatically reflected 
in the orders right so that's very important so   when we check purchase order right we want to make 
sure that this says purchaser right so it changes   the word to purchaser automatically that's why 
we're using that dynamic one here in vendors so we   can change that very very easily and we can change 
it back to vendor so i'm familiar with that so   great we can quickly see there's that extra home i 
was looking for that couldn't find it interesting   so back to home back into orders here we're going 
to go look and also what i want to do is i want to   be able to show dynamically right of course we've 
got some data validation that's going to help us   but i'll make sure we've gone over all the screen 
so again here we're going to use that dynamic   order status right order order type along with 
the status so that it says purchase order status   or whether it shows estimate status or whether 
it shows work order or purchase order style you   get the point right we're using the dynamically 
same thing for here so that status is going to be   done here okay very very good in this particular 
weather regardless of the form type we're going   to have a total in here that's automatically 
going to total it so we're going to let's   just bring up a particular estimate actually let's 
bring up an estimate here or invoice would be   fine here so what i want to do is i want to have a 
formula this formula will always be here we've got   a limited number of rows so it's okay to put in 
a formula here certainly we can use vba to put in   the formula but in this case we can just use this 
if g10 equals empty meaning there's no quantity   then show empty otherwise multiply g10 times h10 
okay otherwise show blank so does this form us   that way anything that's blank as soon as we added 
another item that total is going to automatically   be present as soon as we change the quantity 
that of course total is going to automatically   update great we have the payment amount here 
so if we decide to enter a payment amount here   that payment amount's going to be reflected inside 
here notice this is linked to d12 so d12 is the   payment amount so that is automatically going to 
be reflected we're going to have a subtotal which   is the sum of the total right we're also going 
to have the tax right the tax is going to be   multiplied subtotal by whatever the sales tax rate 
is the sales tax rate and actually that should be   changed right it's going to be either the sales 
tax or the purchase tax right so we should update   that let's make an update because why is that 
important because we have a purchase tax right   this is not going to be accurate for purchase 
orders so i do need to update that if i decide i'm   going to put in a purchase order right we notice 
here's a purchase order this is going to be the   purchase tax which is five percent not the sales 
tax rate so what we want to do is why not make the   update if right our order type let's do order type 
right is equal to the purchase equal to let's say   the invoice type which is the invoice order then 
i'm sure we're good to use that sales tax rate   otherwise right i want to use the purchase tax 
rate otherwise i want to multiply i-35 here times   the purchase tax rate purchase tax rate which 
is right here okay so the purchase tax rate is   different i want to make sure you cleared that up 
okay and then fix that up don't forget the comma   this is then times not and okay excellent so now 
we have automatically so now we can calculate that   and we have the payments here we can calculate 
payments obviously can't have fifty dollar can   have a fifty dollar payment so we have a balance 
here that's gonna look good so that way our   purchase tax is automatically based on whatever 
the purchase rate is if it is not an invoice order   okay good i like that so we have a dynamic taxing 
based on either the purchase order or the invoice   we have the payments amount we have the total 
which is simply the sum of both the subtotal   and the purchase tax and then we have the balance 
which is simply our payment amount minus the   balance amount that's going to give us that okay 
great so we have an understanding of the basic   idea of what we're focused on here we're going to 
be able to search for an order that footer message   we have seen we have that footer message it's 
going to be linked to a formula and if we remember   correctly we do have named ranges associated with 
that for the message we've got two name ranges   one called the footer message text and we'll call 
the footer message name right we have those two   so if i want to extract that footer message text 
we can use an index to do that and we're going   to base it on the footer message that user has 
selected located directly here in d9 if they have   selected a footer message called delivery time i'm 
going to index that footer message text i'm going   to extract that based on the row and i want the 
column and so basically all we're doing when the   end user enters the footer message here i want 
whatever text associated with that to show that   right here okay so when they change that it is 
automatically selected here great so that's just   simply that and i've centered it and increased the 
font a little bit to get as nice footer message   automatically very very good so we understand that 
and also we have the account set up with that now   keep in mind that this is an expense account 
expense accounts associated with the purchase   orders if we have to dispense out however if 
we create a brand new invoice we certainly   don't we want those to become income accounts so 
we've got some data validation that's going to   change if based on that if we take a look inside 
the data we take a look inside the data validation   we see that the accounts associated with 
income accounts on this invoice for cell d11   however if we change this to purchase order we 
see that we have some differences here so if we   take a look inside the data data validation 
we now see that the accounts associate are   the expense accounts right when we purchase 
materials we need them expensed out when we   invoice we need them income so we're going 
to use the same cell and all we need to use   is differentiate between the two different 
accounts listed i'm going to show you how   vba can change that based on of course the order 
type and then we have the payment amount here okay   great so let's get into some of the vba to see 
how all this is working we're going to start with   the on sheet code because there's a lot of sheet 
that's going on code when we select a vendor here   we want the address to show up here right when 
we make a change so let's go ahead and go on to   the on sheet code and so we see what's happening 
here so we're going to click on the order sheet   and we don't have too much code just a little 
bit of code here a few lines of code right sami   along with this so we've got the item row name 
row the found item is arranged so i'm going to   need to look for that item and the order type that 
order type is very important so when a user makes   a change to e10 through e34 what do we want to 
happen e10 through e34 all the way down here   well what i want to happen well if they select 
an item i want to have the item description the   quantity default quantity the price and the total 
automatically appear here assuming that the item   is the correct item that they've selected from 
the list if they don't let them know that it's   incorrect item okay so when they have a correct 
item here and also i want to make sure that of   course if they do type it in i want to make sure 
that it's automatically auto populated so we have   auto population autocomplete right here as well 
using the same thing again we are starting we have   those links notice that those links are down here 
just like we did before remember we too so i have   again i have again here space is located here just 
as we went over before so we have the don't break   the chain method we have all these cells filled 
with values spaces right to make sure that it's   linked to this chain again if we were to take 
a look at that and we were to change it back to   general you would see all of those items appear 
here but they're hidden because they're using the   three semicolons and again we've used that here as 
long as our link so i'm going to control undo that   control z it's going to go back to 
hidden that's what i want so they   can quickly add a new item so if i want let's 
say grind and brew let's type started another g   r i and grind and brew automatically it's going to 
come up okay using autocomplete so what happens if   if this particular item has been found inside 
our item database i then want to extract the the   description on that what is the sales description 
or whatever let's go ahead and put that that   disappeared so we have our purchase description 
here so what i want to do is i want to have the   purchase description the sales description or 
the word description appear based on the type   that's very important how is that different notice 
that inside our orders here take a look based on   we have a purchase description however if 
we're adding a new work order we want our   ad stock value this is going to be our 
work description and then if we click   invoice we have three different we have an 
invoice description that we use on sales   so we have three different descriptions based 
on the form type estimates would also handle   of course the same estimates and invoices would 
both get the same description so for those types   we would also find out that the sales and estimate 
we have the same description purchase orders would   get this description work orders would get this 
description so that's very important so it's   going to be based on the type so we're going to 
so when a user makes a change i want to make sure   that b3 is false what is b3 well b3 is when 
we're loading a specific invoice when we're   loading right this is going to go to true that 
means when i load it that's also a change right   we're also making changes here but that type of 
change is different that type of change i want   to load whatever was previously saved here inside 
our order item database right here and that way   if we make a change to it that change gets saved 
what i mean by that so let's say i want to change   this to commercial brewer let's just say one set 
i want to change this just this just this once   to one set right and i want to save and update 
that right and now if i just clear that open   dove i want to make sure that that automatically 
sets up johnson brewer but i don't want to save   that in the main file so if i put that in again 
right i want to save whatever is in the work order   description here so this is the update so this is 
the work order description i have some older data   here on that so i want to make sure the work order 
description comes up on a work order however if we   add let's say a new estimate i want to make sure 
it is the sales day so if i put in brewer right   i want to make sure that the sales description 
appears here on an estimate on an invoice okay   so let's continue down with the code so we want 
to make sure that b3 is false right b3 means   when they're loading when this is loading from a 
previously this will go to true and we'll explain   that differentiate between that so either it's 
going to come from our items database right here   one of these three values or if it is an existing 
one it's going to come directly from whatever   description we previously entered notice 
we saved this right want to come from here   okay we know that okay so we want to make sure 
that b3 is false we want to set the order type   into a variable that comes from b2 okay we're 
going to set that and also what i want to do is   if e does not equal empty meaning the user has 
entered a value if they have entered a value   we're going to continue what if they haven't what 
if they've cleared it out if they've deleted it   i want to make sure that we've deleted it so we 
need to differentiate between entering a value   here or clearing it out by entering a blank 
if it's blank we're going to do something else   and that is clear everything out if it is not 
blank we're going to enter that so we're going   to first check to see if it's blank if it is not 
blank right here contains text okay we're going   to check it out what do we want to do first i 
want to set the item row i want to know i want   to find out this item but that's very important 
that's the item row from this existing database   i want to look for this item name inside our list 
of item names i want it found and i want to know   the row that's associated with that we can use 
the find command to do that so we're going to   look in the items database we're going to use that 
item name remember not the sorted one the original   one the item name i want to look for the target 
value i mean the one the user just put in and   i want to extract the row out of that using the 
find and i will put that inside a variable called   item row if it has been found it won't be zero 
if it's zero let the user know the best is the   the item was not found however if it has been 
found then i want to move on we're going to use   select case and why is that because remember we 
need to do different things based on that right   i also have a different price right i have a sales 
price and i have a purchase cost right if it's a   purchase order i want to put that purchase cost in 
if it's a sales price i want to put the different   amount in so we can see here it's inside this 
would be the sales so if we put in that first item   here let's say brewery right brewer right i want 
to put in that 8.45 however if it is a purchase   right if we're making a purchase i want to put in 
a different price right so brew here this one i   want to put in that price of 7.49 so this is the 
purchase price and it's going to be based on this   purchase type so we need to differentiate between 
those two so if it is an order if the order type   is in the estimate order or the invoice either one 
of those right sales or estimate then what we're   going to be doing is we're going to be putting 
the sales or estimate description into column f   right that sales or estimate description is going 
to come directly from our column d here d is where   i want to put it directly inside column f here so 
that's going to come from here i also want to take   put that sales price that sales price is going 
to be coming from column h it's going to go into   here h and it's also going to be coming directly 
from column h here right we see our sales prices   at column h our purchase price is in column i so 
we need to differentiate them of course for sales   and for estimates and invoices we want to make 
sure that sale price goes in there so it's going   to come right here continuing on that's if it 
isn't either one estimate what if it's a work   order well if it's a work order i want to take the 
work description and where's that work description   that work description we're going to work for a 
specific item is located in column f right f we're   going to take whatever work description and we're 
going to put it directly in f so that's if it's a   work order also if it's a work order i want to 
make sure that we're clearing the cost right i   don't want to put any specific cost in column 
h i want to clear whatever's there what if it's   a purchase order if it's a purchase order we're 
going to pull in extract that purchase description   from the items database i want to put in that 
purchase description coming from column e in this   case column e is what's going to go into column f 
and i also want to extract that purchase price as   you saw and place that directly inside column 
eight so that's what we're going to be doing   if the order is cases of purchase order column 
notice we're using variables because the user   this is user defined purchase order name user 
defined work order name and so on and so forth so   the purchase order is located here and we will 
be applying the purchase description and the   purchase cost okay we're going to regardless of 
the type of form we're going to set that default   quantity to one and then the user can change it 
so g is going to take on that default quantity   just put in qty make sure okay so now all we have 
that so what we're going to be doing what if it's   else cleared item this is of course this is if it 
contains a value this is if the user has cleared   it right so if it's clear you saw what happened 
right as soon as we clear it what do we want to   do i want to make sure that f through h is all 
cleared out and it's automatically going to clear   out the total all the formula is still there so 
f2h is going to clear it out also i have a save   to database button if this has been previously 
saved let's pull up one that has been previously   oops i click email order don't want to do that 
but we'll show you that in a little bit later   no need to email that just yet nice okay so if 
we have a previously saved order let's pull this   up let's say order eight i also want to have a 
database row associated with that why is that   important if we take a look inside this order we 
see that order one we have a database row four   five six seven eight so if we pull an order one i 
want to save these rows so i know what row inside   the database it is important so we have that 
here so if i pull up order number one i want to   put those and i want to put them right here of 
course they're hidden just like the others are   we don't need to see that but if we were to 
change it back to general we would see that   four five and six are saved these are the row 
numbers that have been here right so these are   the database row numbers if i delete one i want 
to make sure that we also clear out the row number   and when we save it we'll also clear it out so we 
want to clear that out in case we need that okay   so j actually j through k we need that just as j 
okay but actuality if it contains a database row   we should also clear that database row out as well 
so that's important so let's write that up right   now right we got to make sure that it gets deleted 
it should get deleted so if right let's check on   that j contains a value if column j contains a 
value we want to make sure so how do we know that   we don't need this clear content it's j right j in 
the target row clear contents we want to clear it   but i don't want to clear it just yet what i want 
to do is i want to clear the database row first   so if let's double check this range j contains a 
value dot value does not equal empty right then   we know it's been saved in the database then i 
want to make sure to clear that out that's super   important then what are we going to do we're going 
to do a few things right i want to make sure to   clear it out in the database so we're going to set 
the database row so we're going to dimension the   let's say item database row as long okay in case 
we have it i don't think we've used it here let's   see up item nope we haven't set it as a variable 
so once we do that then i'll just want to do this   inside the item database order items database 
dot range okay what does that range we need the   item database we're going to put that we're going 
to define that just an item database row and the   item database row dot entire row dot delete we 
need to define it though so we'll do that right   now so we're going to delete that entire row and 
add the and sign there and then what we'll do is   that what is that it's going to be this one right 
here we're going to set that into a variable here   and that is going to be the item database row 
item database row equals here that value good   so we've got that set up so now what it's 
going to do is we're going to delete that   item directly inside the database if it exists 
right that means it's been previously saved   so let's give that a try we know it's 
been saved i decide i want to save this   let's refresh that order once again load it up 
because we haven't deleted it it's here right   so now when i decide to delete it and i want 
to save and update that order it is now going   to be automatically deleted so when i load that 
up again you see we only have those two items   perfect that's exactly what i want we're going 
to delete the item from the database only if it   exists of course if it doesn't exist if we haven't 
yet added to the database we don't need right so   now it doesn't have a database rule associated 
with that so that's great so continuing on so   we've got that here we set the default quantity 
we've cleared out the database if it exists here   and now what we're going to do is that's it 
that's all we need to do is simply add items but   what about when on a name change i want to set the 
address right if i make a change to this name and   it contains an address i want to make sure that 
that address the city the state and the zip all   get put into the following lines so we're going to 
do that with this line of code user makes a change   to f5 and i want to make sure that f5 is not empty 
then what we're going to be doing i also want to   make sure that b8 is not fb remember b8 is the row 
that's associated with that it's like the name row   if it does contain a row if we know the row then 
we can then extract the address and extract the   cd and state directly from that row from our 
names database right here because we know that   that address is going to come from d the city the 
state and the zip are going to come from e f and g   respectively so we can put those in accordingly 
so we do that with the following lines of code   we're going to get that name row we're going to 
extract that from b8 i'm going to take whatever's   in f d remember which is our address and place 
that directly in f6 and then what i'm going to do   is i'm going to place the city coming from column 
e in our names the state coming from column f   and the zip code coming from column g and place 
them all directly inside f7 separating them by   spaces and a comma here that's it that's all we 
have to do to simply automate the address into   line rows f6 here and f7 pretty simple there it's 
assuming that we do have row okay continuing on   there's another change i want to focus on here 
inside d8 notice when we load it when we could   change the da and we search for a particular order 
we can load that order based on some change events   to d8 so that is what's coming up next on a 
search change order when we make a change to d8   and we want to make sure the d8 doesn't contain 
empty then what i want to do is i want to check   the first thing what i want to do is i want 
to search it based on a row if we take a look   at this one we have the search id row here so if 
i were to stop this code right here let's take a   look and then what we're going to do is we're 
going to make a change to here enter 1 here   and we see that inside the code we automatically 
have a search row this search row is based on   a match based on d8 we're ordering i know that we 
have the order search row this should probably be   adding three but it's okay either one is okay 
plus three that's the correct row of the order   but it works either way i know that it because 
it doesn't contain empty that it is a correct   search order number right we know it's the correct 
order number because it contains a value here in   b4 right so we're going to base it on the match 
so assuming that it is correct then we know we   can take this order id and i can place it directly 
inside our located b5 i can take that id and put   it in b5 and that's just what we're going to do 
in the direction code if b4 doesn't equal empty   if it is empty please make sure to enter correct 
order id right if it's incorrect right we can do   that but if it's not empty what do we 
want to do i'm going to take whatever is   inside d8 that order number i'm going to place 
it directly inside b5 taking whatever order id   the user has put in here and putting it directly 
inside here so that way any order id they enter   is automatically go inside here b5 once they do 
that it's automatically going to generate a row   using the match formula it's going to generate 
that row and then what we can do is we can then   load the order so once we generate that row we 
can run a macro called order load and we'll go   over that in just a moment but i want to go over 
the remainder of the macros inside this on sheet   now notice we also made some changes remember here 
correctly when i made a change to the order date   or when i made a change to our terms we want 
that due date for both invoices and purchase   orders automatically to update based on that so 
that's simply going to be a change based on either   on i4 or change based on i6 and certainly not 
on the order load so the changes when a user   makes change but not when we're actually loading 
in the order so not those types of changes okay   so we want to differentiate between different 
types of changes to make sure that we're   loading the proper order okay so how do we make 
the changes so if the user makes a change to i4 or   i6 and we know that the order is not being loaded 
because b3 is false we then want to continue so   i want to also make sure that we have values i4 
meaning the order date i6 meaning the terms i want   to make sure that those aren't empty if either 
one of those are empty we can exit the sub out   i want to get the term row this is very important 
because i want to extract inside the admin i want   to know what row this term is what row is it row 
anywhere 15 16 or not all the way through 19.

Once   i know the row i can extract the number of days 
once i know the number of days i can then take the   number of days add it to the purchase order add it 
to the date here and then we can automatically get   the due date so we know that if we simply change 
this to 30 it's automatically gonna extract that   30 is going to be found looking on row 17. it's 
going to find this and we're going to be able to   simply add that to the order date to to get our 
due date so that's exactly what we're going to do   inside the code we're going to determine the term 
rows long we're going to get that term row what   we're going to do is we're going to look for that 
based on whatever is in i6 value this is our terms   we're going to look for it inside our named range 
called terms and we're going to extract the row   if we've found it correctly we know 
the term row is not going to be 0.   i7 which is our due date is simply going to be our 
our order date plus whatever was found on column   m and the term row column m in the term row of our 
admin screen right here column m and the turn row   is going to extract whatever number of days there 
and we can use that to simply calculate our due   date very very easily great so that is all we have 
to do for our particular worksheet change we just   went over all the worksheet change events located 
in there we only have the selection change a few   ones we've got two pop-ups here and i just want 
to make sure this pop-up and this pop-up i want   to make sure that if the user selects anything 
that those disappear right these two pop-ups   this one is going to be called add new group 
this one is going to be called copy2group so i   want to make sure that both the add new group 
and the copy 2 group are not visible when the   user makes a selection if they are visible we 
should hide them on selection change so if add   new group is visible when user makes any kind of 
selection change then hide it and the same thing   with the copy 2 group if it's visible hide it so 
that's all we're doing with the selection change   so let's take a look at some of the order sheet 
macros i've got just a few of them here on this   order sheet and then next we'll get into the order 
macros now remember we had add an edit name we had   this on transactions it was very simple this 
is the macro that we're going to be doing when   i want to add and edit that name right so it is 
exactly the same but just with a few differences   inside that so it's automatically going to load 
up that name and of course if it doesn't exist   right if if it doesn't exist let's say we have a 
new order let's say add a new estimate and we put   in a new name right that doesn't exist i want to 
make sure that we're loading that when we load up   we load that up no it doesn't match restriction 
to find the cell that's okay and let's update   that i do want to update that data validation 
actually i'm going to update that through the code   because i want to make sure that the code takes 
on notice that we did make that change but it   is the code that does it so i'm going to go over 
that so basically the code automatically added the   restrictions if we take a look inside the adder 
restrictions we updated that but i want to make   sure that that update that error alert inside the 
code is unselected i'm going to show you exactly   how to do that so that the error alert is simply 
just a warning and or simply allow and not the   error alert okay so that's going to allow us to 
enter anything we want because i want to make sure   the user can enter anything we want if they want 
to do that and how do they do that well inside the   code right we don't need that right so we want 
to update the validation so if we go into the   data right in advance we need to make sure that 
the error alert this is what we want click ok now   we can do that but we want to do that inside the 
code why is that important because the code that's   going to change this data validation it's going to 
update that from customers to vendors because it's   going to be on a purchase order so i'll show you 
how to do that because once we do that we can then   add james right so now james has been added to the 
list and i'll show you how to do that inside the   code so that's we're going to get into right 
now but i want to make sure that when we edit   either visit a new name or an existing name if we 
remember correctly both 8 and 9 is going to let us   know that right so if we enter a brand new name we 
know that b8 is going to be empty and that's going   to help us out so with this when we click on that 
button right here this is the macro that's going   to run let's add an edit name we're going to set 
the name field to control the name row is long   just like we did before we're going to clear out 
that entire form that form all the fields in that   formula to be cleared out we're going to focus on 
the orders again if b8 here is empty we know it's   a new name we're going to do exactly the same 
thing we're going to set whatever's in f5 this   time set it to that new name we're going to set 
the customer name here that customer name is going   to be set to the type value right i want to know 
its customer here set to fall to customer or event   actually should say customer here customer name 
probably should be the customer or vendor right   if it's a purchase order we should send it to 
customer if it's a vendor let's do that huh shall   we if right dot range b2 dot value equals right 
what is that customer order we'll set that up   right i want to know if it's an invoice order 
invoice order then we know it's an invoice then we   want to set a customer else we want to set it to 
vendor then vendor so then right customer else and   then end if okay so how we do it so what i want to 
do is i want to set the default name if it's a new   name probably most likely user can change it right 
so either to customer if it's an invoice or vendor   if it is a purchase order so this is else purchase 
so that's going to be here purchase order purchase   right probably purchase order so i would say 
i would let's change this to purchase order   i'm going to change this because really only 
purchase orders use that so purchase order i'm   going to check that name range to make sure that 
we got it right so if we go into the formulas   name range i want to check to make sure that it is 
the purchase order here this is the one i want to   know purchase order here purchase order if it's 
a purchase order change it to vendor everything   else change it to customer that's going to be 
sufficient because both work orders and invoices   and estimates all use churches all use our 
customers while purchase orders use the vendors so   we're going to change this up if it's a purchase 
order then we're going to set this to vendor name   right otherwise customer name vendor otherwise in 
every other instance use customer that's going to   be much better so that way we have automatic 
default and we're just going to change this to   vendor good okay i like that and i'll show you how 
that's going to work and why that's so important   all other orders and then this is only going to 
be for our purchase orders purchase good here's   why i like it so now we enter let's say a brand 
new let's say invoice right and i decide i want   to enter something new right if it doesn't exist 
okay no problem i want to add a customer right so   we figure it's a customer if it's on an invoice 
but what if we're on a purchase order if we're   on a purchase order we decide to add something 
brand new right then what i want to do is i want   to make sure the type is vendor right default it 
to vendors so that's all we did is differentiate   the default to that vendor so i think that's 
kind of a nice addition and that's exactly   what we did here so we just differentiated 
we're setting the default here okay what i   want to do is i want to set the name bro if 
it's an existing name this is existing name   i want to set the default name row to ba so you 
want to set the name to ba then again we're going   to do exactly the same as we did just run that 
loop setting the name field as we've been over   set that name field to those controls starting 
from name one three minus two starting at one   going all the way to nine and then just setting 
the values based on whatever's in there and then   saving that and we're just going to simply load 
in whatever is currently in the database here in   the names database loading it into that form using 
that loop then all we're going to do is just going   to update the name type to our name type field and 
of course the status field we're going to update   from column j and then we're going to display the 
form so basically this loads all of the data from   whatever's in the database up into that form and 
that way if we have a brand new one we can load   it's going to load the name in however if we have 
an existing one it's always going to load in that   name here so that's exactly how we're going to 
do that all right great so we see how that loads   it in directly from the order form and now what 
we can do is move on into adding the new menu   remember when we selected this here it has a new 
menu so that's very very easily we have all we   have to do with this macro is simply display this 
particular menu right here i want to display this   sub menu which is just a group of four different 
shapes all together grouped together called add   new group so it is that add new group that i want 
to display when we select this button here so when   we click the order add new menu all we're going 
to be doing and it's going to be a toggle and that   what does that mean the toggle helps because if i 
click it again and again again it's going to hide   or show or hide or show and that's exactly what i 
want so what we have to do is we have to check is   it currently visible if it is hide it if it's not 
show it and that's all we're going to do inside   the code if the order shapes add new group is 
visible equals true then hide it otherwise show it   that's it and now what i want to do is i want to 
do the same thing with copy to menu now if i have   an existing let's go ahead and pull up an existing 
order and i want to create a brand new one we're   going to use copy2 maybe i've got a purchase order 
and i want to copy it over to an invoice so i can   do that just with this one line of code copy 2 is 
almost the same we have a same group but this is   called copy2group and again it's just another 
toggle how we're going to do it so that's all   we have to do inside the code here is simply just 
check to see if the copy to group is visible if it   is hide it if it's not show it that's it that's 
all we have to do so just a few macros on this   okay now we're going to get into the order macros 
we have the order macros this is how we're going   to create these new orders first thing we're 
going to do all these variables i'll be going over   as we go through the code we have order type as 
a string i'm going to set that as the order type   this is going to be the save or update this is the 
order that's going to actually save it's the order   tied to this button and i want to save or update 
it if it is in a brand new order new estimate or   new order we know that our order row is going to 
be empty located in b6 because there's so if it   is not it is going to be an existing one right if 
it's an existing one we know that it's going to   automatically have a row located in b6 so that's 
going to be our differentiator but the first thing   we want to do is we want to make sure that we do 
have a vendor a customer located in f5 that is a   required fields if it is not we want to let the 
user know to please save it now i like a nice   dynamic right if we add a new one and i want to 
know are we going to be adding it so if i try to   save this it says please be sure to add a customer 
before saving this estimate i like that because   it's dynamic and if we take a look at the purchase 
order and i try to save this it's going to say   please be sure to add a vendor before saving this 
purchase order so notice how vendor can switch to   customer switch to vendor and purchase order 
switched to estimate switch to purchase order   because we're using dynamic variables inside that 
so meaning if it's missing a if it's missing a day   if she's missing that name we're going to show 
please be sure to add whatever's in f5 e5 and   what does that mean if i was going to be either 
the vendor or the customer that is the name based   on here right it's based on a formula so please 
add a vendor or customer based on whatever's in e5   before saving this whatever it is in b2 which 
is our order type right here before saving   this order type also i want to make sure that 
they've added a date that date located here in   i4 is very important we want to have an order date 
whether it's a work order purchase order invoice   or estimate we want to make sure that it contains 
a date so if i4 is empty please sure to add an   order date before saving this order type whatever 
type it is so we don't need a type number that is   unnecessary if it's in we need to different just 
as i mentioned before is it a new order meaning b6   is empty or it's an existing order if it's the new 
order we're doing just like we did before in items   and also names we're going to get a row being 
the first available row in the orders database   we're setting the next order id based on the max 
so we're simply taking the next word id and we're   going to place it directly inside b5 here and then 
also what we're going to do is we're going to take   that same order id and placing it in the first 
available row inside column a inside the orders   database however if it's an existing order all we 
need to do is extract that order row from b6 and   we want to set some variables i want to enter the 
order id into a variable i want to know the order   date inside a variable i wonder the order status 
also the status is very important here the status   is going to come directly from d10 so i want to 
put that inside a variable and i want to know the   account that's associated in d11 that account is 
important for both purchase orders and invoices in   d11 you could probably require this as a required 
field as well located in d11 and then what we're   going to use is we're going to use data mapping 
all i'm going to do is again take the information   whatever's in these fields here and map them 
to the directly to order database that means b2   or i4 is going to take on that order date here 
d10 f5 are going to go inside these columns we   know whatever's in i5 is going to appear in the 
employee column i6 and so on and all the way   through column l and we do that here for the order 
column equals 2 to 12.

We're simply updating the   orders database based on the order row and order 
column whatever is located in those ranges those   ranges located in row one so we're going to save 
the data to the database now what we want to do   is we want to update the items right we've saved 
all the information inside the order here all this   information but what about the items we need to 
make sure that when we've added items we want to   make sure that those items get saved as well and 
we don't know if they've been saved previously   or not but we're going to look inside column 
j if they have not been saved if there's no   database row associated we will assign a brand 
new row if they have previously been signed   we can use the existing row it's hidden here using 
the three semicolons but we know it is there so   we're going to do going to check on that first but 
first we need to determine the last row of the i   items based on e34 and excel up row that's going 
to give us the last slider mode so that we can   loop through all of the items as we loop through 
all the items in this case the last item is row   12 so we'll loop from 10 to 12 and all the items 
if the last item row is less than 10 then go to   no items right for the item row equals 10 
to the last item row we're going to check   column j is there a database row that's already 
associated with it if it's not empty we have an   existing database row we're going to assign a 
variable order item database row that's a long   variable based on whatever's in j if it has not 
yet been saved then we need to create a brand   associated brand new order we're going to look 
at the first available or row inside of course   our ordinance database plus one that's the first 
available row our order items database is located   right here so inside this database right i want 
to find the first available which is 70 right   inside that we want to add the order id the 
order type the date the status the account   then the item information total in the rows i want 
to add all that information inside the order items   database and so we can do that here so we're going 
to set the row if it's a brand new one right we're   putting in the order id i'm putting in the order 
item row located in k right k is going to take   on that order more k is going to take on the row 
of the item 10 11 12.

Remember it's going to here   this row 10 11 12 whatever row we're on we're 
going to also want in the particular formula this   row format to take on the database row so we're 
going to have to set that up in column l and we're   going to do that right here column l it's going 
to take on this column l is going to take on this   formula right here then also if it is brand new i 
want to set that order added item database inside   j so that we know that it's been already saved 
okay everything else we're going to do regardless   if it is the new item or the existing we're going 
to inside b we're going to put the order type the   order date the status and the account i'm going 
to put in those respectively bc d and e columns   respectively then also in a single line of code 
all the way from f through j we're going to take   on here item description quantity and amount and 
the total all the way is going to come directly   from our orders all the way here from e through i 
and bring in all those information over here great   so we've brought in those information that's 
it that all we have to do is loop through the   item and then just run our fade out message saved 
message and this is the same type of faded message   that we've gone through before so we don't need 
to go again so when we go ahead and save those   orders it assigns those rows they're not hidden 
now we've previously hidden them just remember   home general custom three semicolons we'll 
keep them visible for the training right but   three semicolons will hide them right here so we 
we've added that up and of course if we decide to   add a brand new item to that right we know that 
it hasn't been assigned a row so we would have   to assign a new row as soon as we save it it's 
going to assign that new row to that that's it   for how we save the orders but what about a new 
order new orders are a little bit more tricky   because we don't know what type of order adding a 
new estimate adding a new work order adding a new   invoice or adding a new purchase order and it gets 
even a little bit more tricky because i decided to   add more features maybe from our main menu here 
i want to add a work order or maybe from our main   menu i want to add a purchase order so i also add 
an estimate so how do we know just based on that i   want to use a single macro for this so if we look 
here we've tied a macro if we can that's going to   be the same macro so if we click n here we see 
i'll bring it up we see it is this called order   new macro so it's the same order it's the same 
macro whether i click here or when i go into the   orders and then we start a macro it is the same 
if i click on one of the individual buttons here   click assign macro here we see it is the same 
macro order new so it is that macro so inside this   macro we're going to need to differentiate between 
where they are okay so if they're on the home   screen i want to do this i want to set them order 
types right if else from the order screen we're   going to do some other things okay so based on 
where they are being sent from we're going to make   some changes okay so the first thing if they're 
inside the home screen i've given these particular   names right this is called estimate this button 
if they click it this button name is called work   order work underscore this is called invoice and 
this one is called purchase order purchase order   so if they click this it's the same macro but the 
name of the shape that they click is different   based on the name of the shape that they click we 
know to add what shape which order to add so we   can use that here so if the active sheet is home 
we're going to access from the home screen the   order type is going to be whatever the name of the 
shape that called it but i really don't want those   underscores so i want to remove those underscores 
and replace them with shapes so we know that our   order type is going to be invoice estimate right 
and however but those are not the dynamic names   right those are the hard-coded names those are the 
these are these names what if they've changed it   to proposal right i need to make sure that that 
proposal right is the one that goes in here right   so that's very important so how do we extract that 
well first of all what i want to do is i want to   get the row we know if it's estimate i want to 
look for the word estimate from b8 to b5 and what   does that mean inside the admin i want to look 
for this estimate if it's found on row 5 i want to   return whatever's in here so that way if i change 
this to proposal and we go into the home screen   and i this change deposit when i click here 
i want to make sure that proposal goes here   not the word estimate that type should 
change to proposal very very important   so inside that it's also important that we update 
that so what i'm going to do is i'm going to look   for order type which is estimate let's take a 
look i'm going to pause this right here we're   going to go into the home screen and i'm going 
to click this okay i want order type is estimate   right because that's the name of the application 
caller the shape name of the shape that called it   so what i'm going to do is i'm going to look from 
b5 to b8 i'm going to look for estimate i'm going   to find what row it's been found on it's been 
found on row 5.

Right if we take a look inside is   we see that it's found on row 5. so if i know 
it's founder row 5 i want to put in the type   whatever is located in c and row five so as we 
continue the order type now becomes whatever's   in c and row five so the order type if we move 
through this a little bit we see the order type   now becomes proposal we've updated that i also 
want the status type the status type is going   to be based on whatever the application caller is 
in this case estimate or edit so that's fine the   status type why is that important the status said 
because we're going to have to set some status   right we need to differentiate our status here 
is different right we have different a different   dynamic data validation based on whatever 
type it is remember that remember that   here inside the admin we have this proposal we 
have this so we have all different types so i   need to know which one to use that's going to be 
based on that i will change this back to estimate   and then we'll click it again to make sure that we 
have everything's functioning properly clicking on   here it's going to add it okay let's continue 
on with the code let's reset the code here   and then we'll undo this we don't need to stop 
anymore continuing on with our home page and going   to a brand new estimate i really like the dynamic 
nature of this it's very flexible so what we can   then have is continuing on with the code so we're 
going to activate the the order screen right we   want to activate it we don't want to activate the 
home screen we're going to set the status what if   it's from the order screen well if it's from the 
order screen a few different ones if i click here   we see that we've got two things i've got estimate 
and estimate i've got two different things now   this is going to be interesting here we've got 
two different values here that we can extract one   is the name of the shape okay and two is the text 
within the shape so let's go ahead and change that   again oops that's okay let's go i want to change 
it one more time and i want to show you again now   how we can propose all okay so we add a proposal 
now and now we're going to go back into the orders   now when i click add new you see proposal is 
here so if i've changed the text here proposal   i can take this text which is the estimate or 
we're using that named range right estimate order   to change the text here i'm going to use that 
text and i'm going to place it directly inside the   order type and i'm going to use the name of the 
shape estimate as our as our status type because   we'll all go into the status type and why that's 
important okay i know it's a little bit confusing   but it's valuable so when i click proposal i 
know to take whatever's the text of the shape   the text right the text inside the shape and put 
it here and that's exactly what we're going to do   inside here the order type is simply the text of 
that shape the text right the text of the shape   the status type is the name of the button right 
where remember every shape can hold two data   values one that's what i like shapes are so great 
because they can hold a lot of information it can   hold whatever's in the text can hold one whatever 
the name of the shape can hold another and here   we're extracting two different values from the 
same shape however there's one other thing when   we delete an order when i delete it i will always 
want to make it automatically go to this so let's   go ahead and there's something else here when we 
delete an order i want it to set a default to new   invoices so let's go ahead and just create a brand 
new order here just a simple order some estimate   and then we're going to delete it and then i'll 
show you so i'm going to save this order and then   what i'm going to do is i'm going to delete it i 
should want to delete it and then i want to set   the default this process has been deleted and i 
want to set the default to go back to invoice when   we delete an invoice we also create a brand new 
one so if the user has deleted it where do where   should we set the default what should it go back 
to i just say let's go back to invoice and show   clear invoice so that's all we're going to do if 
the order type is delete order it would be delete   order because because the name of the button is 
called delete order the shape of the name that   they've used is delete order we're setting that up 
here then what we're going to be doing is setting   the order type equal to the invoice order and 
the status type to invoice so basically we're   just setting the default it's invoice if they've 
deleted the order the order type would be delete   order when we delete it okay all right great so 
continuing on what we want to do is i'm going to   set that order type right b2 is going to take on 
whatever order type you saw that automatically b2   updated automatically as we add it inside that and 
then what we're going to be doing is i want to set   the order type number now i want to set a number 
based on their order type basically one through   four and we can simply use the match based on the 
order types the order types is the named range   based on this it's a dynamic actually it's not 
dynamic it's just a named range called order types   based on these four possible so as they change 
we're simply going to look for proposal and we're   going to find it and we're going to return one 
two three or four and we're going to have that   that's going to also help us inside that so back 
into the orders we go i'm just going to return the   one continuing on with our code so we're going to 
set that order type into a variable long variable   it's going to set that order number b3 i'm going 
to set this to load right i'm going to set it to   load and then by the end we're going to set it to 
false i'm going to clear the contents of all the   order fields just using clear contents i'm going 
to set the current data's default in i4 and i want   to set the next order id it's a brand new order so 
whatever that next order id using the max formula   here we're going to set that up into b5 so b5 is 
going to take on whatever's in b7 and that's going   to set the next one i also want to basically hide 
that add new grip remember when i select something   a new work order i want that group of shapes to 
be hidden so that's going to be hidden right there   then what i want to do is i want to clear the 
validation in three different cells d11 d10 and   f5 so let's take a look at that let's take a 
look inside an invoice here okay so d10 is our   status d11 is our income account and then we have 
our either customer vendor here so those are all   validations but they're dynamic based on the 
form that's used right because we know that   this is going to be a status if we take a look 
at this status and we go into the validation here   we look at the data validation we see that this 
is called status invoice status invoice so however   if we create a brand new one let's do purchase 
order and we take a look at this status here   we go into the data validation here we see that 
this is status purchase order so what i want to   do is i want to make sure it's dynamic but before 
we update or change any validation here here here   we must always first delete any validation 
so that's the first thing we're going to do   all three of these they get their validation 
deleted okay and now what i want to do is i   want to add i want to add a brand new i want to 
set that status type this is where that status   type is very important remember that status type 
is either invoice it is purchase underscore order   work underscore order or it is estimate right 
and that's going to go in here so that's going   to be the status and the status type why is that 
important because look in the formulas and name   manager let's click on status right we have status 
estimate status invoice status purchase order and   status work order so those all pertain to the 
individual statuses here in our admin screen so   i want to set the data validation based on those 
so that's very important that we do that so we're   going to set it so this way we can dynamically 
place the status the formula of that's going to   change automatically if there's an issue we'll 
wrap it around on there okay great so now what   we need to do is we need to add additional 
validations right we've added the status here   no matter what it is the status is going to change 
no matter what now i want to add a validation   income or expense accounts here i want to add a 
validation vendor or customer here so we can do   that with the following based on the order type 
so we're going to use select case based on that   order type if the order type is an estimate i want 
to make sure that that customer name validation   gets added to f5 also if it's a work order i also 
want that customer name added i guess we could add   it here and here if it's in in other words we 
could use a comma here and then work order that   would probably be okay too so we could combine 
these two that would be pretty easy but if we   want to add additional things to estimate our 
work order okay but if it's an invoice order i   want to do a few things f5 of course is going to 
take on the customer d11 is going to take on our   account income remember i want the income accounts 
associated with this we create a brand new invoice   i want d11 to take on income accounts only we have 
income accounts we have accounts income accounts   accounts income here and we count expenses expense 
is going to be for purchase orders and income is   going to be for invoices okay so we have both of 
those here both expenses and income here okay all   right so expenses was here okay all right so 
we have both of those but i want to make that   update so we know that if it's an invoice right 
our validation is going to be formula accounts   income however oh one more other thing i want to 
set the default income account remember if we we   have sales income that income came from a direct 
location that came right from here it's called our   income invoice income account named range located 
here in g11 i want to set this as our default   account if i were to save it change it to other 
income and then if we create a brand new invoice   here and it's automatically going to change to 
other income as i've already demonstrated for you   previously so i want to make sure that that gets 
done and that gets done directly inside here so   we're going to place that right here invoice this 
named range invoice income account is going to set   the vault into 11.

Okay great well that's all if 
it's an invoice but what if it's a purchase order   if it's a purchase order we want to add the vendor 
name right we're going to be pulling down vendors   so we know that when we added or a brand new 
purchase order i want that drop-down list to be   a list of vendors so if we take a look in the data 
validation here we see that it is vendor name as   opposed to an invoice where it would be customer 
name so that's what we're differentiating invoice   work order or estimate would be customer name 
so you see the difference in the data validation   that happens directly through here 
this formula gets added based on that   okay in d11 we want to take the account expenses 
remember it's expensive counselors associated with   purchasers so we want to give the user the option 
to select which expense accounts but we do want to   set the default expense accounts as to whatever we 
added directly inside here called purchase expense   account so whatever purchases are that is the one 
that we're going to be using inside those orders   for purchase orders so that's the one that we want 
to set for purchase orders that material purchase   is going to show up directly inside d11 right 
here great so that's it that's all we need for the   validation that's updater validation now we just 
need to update default payment terms and purchase   orders on invoice and per servers remember invoice 
and purchase orders we've set a default payment   term right it's net 15 but if i want to change 
that i can change that to net 30.

I want to make   sure that we look for that default so that when 
we create a brand new invoice or a brand new   purchase order that default gets set automatically 
right here into i6 so how are we going to do that   well what we're going to be doing is i want to 
determine what row is that status what i want   to do is i want to look for that little check 
mark inside our status here we've got a named   range called here actually i don't think from d 
i'm not sure i don't think we have a name rich   n15 through n21 i want to look for this check mark 
now that check mark inside vba or character code   is called character 252. so i'm going to look for 
character 252 how do we know that it's character   252 well if we insert right a symbol and we want 
to insert a symbol here we can look for that check   mark here and we see that it is character number 
250 oops and we see that as character number 252   okay so we know that it's character 252 from 
our character there so what we're going to do   is we're going to look for that so here i do have 
default stats or status default and status type   what we're going to be doing oh sorry this is for 
the default status sorry we're going to get to the   default oh here we go default payment returns 
i almost skipped it default payment terms here   then we're going to focus on status then footer 
message okay so again as i said that named range   and 15 and 21 i'm going to look for something i'm 
going to look for that character 252 that's that   little checkbox and i want to extract the row from 
it and i want to put that row inside this variable   called term row if it is found meaning it is not 0 
all i want to do inside i6 is place whatever is in   l in the term row what does that mean that 
means we found where there's a check mark we   found that it is on row let's say it's row 
uh here 17.

I want to return whatever's in   l and whatever's been returned whatever that row 
that it's been found it's been found on row 17   so i'm going to return whatever's in there l okay 
so that's all we need to do and we're returning   that directly inside here and that's going to 
place directly inside i6 great so we have that   set up and that's all we do now all we have to do 
is default status based on the order type remember   we've got some default statuses too pending 
remember we showed you that purchasers have   pending whereas work orders may have unassigned so 
we have default status we've set that up directly   in here our default status is here here or here so 
our default status is whatever we've checked again   we're going to look for the character but we want 
to look for it in a specific column now how are we   going to find that well what we're going to do is 
we're going to find it in here default status and   the status type status default i've created named 
ranges for this let's take a look inside that   formulas name manager and it's going to be called 
status default estimate here we've created one so   it's here actually this should be one more row 
probably and then what we're going to have is   status default invoice here so here oh it's 
dynamic that's good i like that there so it's   dynamic based on that which is perfect so what i 
want to do is i've created a named range called   status default and then whatever it is so what 
i'm going to do is look for that look in that   named range status default look for the row that 
has been found and then return whatever's in the   previous row so how are we going to do that well 
we're going to set the row we're looking for that   character we're looking at for we know the status 
type is estimate purchase order invoice or work   order so this completes the named range we're 
going to look for that checkbox inside that   we're going to extract the row if we have the row 
then all we need to do in d10 is going to go admin   default status row right and the add we know 
the row but what is the column the column is   based on whatever column our range is here what 
is the range of that status in the status type   this is our range where it's found i want to 
extract the column where that range is found   so our status could be status invoice status 
purchase order i want to return the column that   that range is found out if i know the column here 
and i know the row i can return exactly the status   that i want so that d10 is going to place the 
default status based on whether whatever the user   has set up directly inside here so we know that 
if we decide to set this to ordered on brand new   purchase orders and we create a brand new purchase 
order we know that that default's going to be set   to ordered great but we also have footer messages 
the last default that we need to set up right   we create a we have default footer messages i know 
it's comprehensive i'm telling you i hope you have   more than one coffee so i've got our defaults set 
up here here here and here so we can do based on a   column so let's take a look at what column number 
that call number is going to help us we know that   estimates are going to appear in column 18 19 20 
and 21 okay so we know based on estimate workers   so if i know here's where it's going to come in 
handy that number if i know that our default is in   column 18 and i also know that if it's an estimate 
here proposal estimate same thing that this is   order type number one i know what column to look 
for it in so that's going to help us right because   i know it's a column so our default footer message 
row what row are we going to have it on right i   want to know i want to extract i want to know 
where the check mark is right for the estimate   this is our estimate so if i know the column i 
want to know what row is the check mark i need   to know what row to put this where to find the 
check mark is this our default estimate message   or is this our default so having that in there 
it's going to put that in here so we continue on   so we're going to look at that so it's going to 
be based on here our admin we're going to look   through a range and where's the first row the 
first row is 28 that four looking in first row   is 28 what is our last or our last row is 39 now 
what column it's going to be column 17 plus our   order type if our order type is 1 meaning estimate 
it's going to be here if our order type is two   work order three invoice figures so our order 
number is going to help us determine what column   so we know the first row but what is our order 
type remember this is one two three four plus   seventeen is going to be our column so that means 
the estimates would be on column 18 and so on so   i know that so this is going to develop our range 
this is a dynamic range so our order type in all   the way to look so we're looking from row 28 to 
row 39 inside column 18 19 20 or 21 and what are   we looking for we're looking for that character 
252 and when it's found we're going to extract   the row of it that row is going to tell us exactly 
what that footer message should be as long as that   row is not zero then d9 is simply equal to admin 
f and the default footer message how do we know   that we've extracted the row we're going to take 
a look inside f we've got the row and i'm going to   place whatever has been found in that here and i'm 
going to place it directly inside here our default   footer message located in d9 and that sets up our 
default footer message great now also we have copy   2 this is almost exactly the same and copy cue 
is a great little feature because it allows us to   quickly and easily copy whatever we have if we 
have a work order let's say we've completed the   work order and we want to now create an invoice 
based on this all we need to do is just click here   and it's going to create an invoice based on that 
we can just update the totals if we want to very   very quickly it can automatically copy over or 
maybe we want to take an estimate or an invoice   and automatically create if we've created let's 
find an estimate here i think or maybe purchase   order there we go so let's say we have this 
estimate we want to also create an invoice   based on this estimate all we need to do is use 
copy2 and it's going to automatically create an   invoice based on that so i like that ability to 
have that and so how are we going to do that well   of course we're going to use mostly what we have 
we're going to set some very specific just as   we did before we have some very specific names in 
here estimate here we're keep remember this stays   the same as our estimate this the text updates to 
whatever the user has changed it to so notice that   the name of the button is still estimate but 
the text is proposal that's going to help us out   tremendously so the order type the order type is 
based on the text inside the write the shape in   this case proposal the status type is based on the 
name of the button which is estimate so the status   type stays the same it's not dynamic however 
the order type does b2 is going to take on the   order type just as we did before the order type 
number is going to stay b1 just like it did before   everything else is the same really all we need to 
do however there's a little bit different we're   going to hide that copy to group right so we're 
setting the order load false doing everything we   have and then what we want to do is we want to 
make sure that we uh setting the order load to   true it's good enough as it is i like it the way 
it is okay so there's not too much difference here   so again we're taking we once we have extracted 
the order type and the order says everything else   stays the same everything else stays the same 
however there's a few differentiates we're not   when we clear out the information remember we're 
clearing the contents there's a big difference   here clearing the contents here and clearing 
the contents here notice there's a lot more   fields when we're adding new and we're copying to 
right why is that because when i create a brand   new one i want to clear out all the fields however 
for copying 2 i want to clear out only specific   fields i want to keep all of these fields here 
but i do want to clear out any database rows   associated with that i also want to clear out the 
order id actually order id when we copy two should   be cleared out but notice the order oh actually 
that's fine we're taking the next order id we're   placing it here the next door id has not been 
entered in the database which is fine so our order   row is empty and this tells excel make it a new 
order so as soon as we save this a new row gets   assigned we have new database rows assigned so 
when we copy to we're not clearing out everything   we're only clearing out things that are associated 
with the database we're clearing out the order id   we're updating this to the next order id so that's 
why we're clearing out just more limited fields in   our copy to here limited fields not in the we 
certainly don't want to clear out all the data   inside we don't want to clear all the items so 
that's very important so that's it for copy two   everything else is the same because once we 
copy everything to when the user clicks save   it assigns a brand new id it assigns a brand new 
row and all new database rows so that that way it   gets assigned a new record very very easily to do 
that that's copy to what about order load remember   order load that's what happens when we click one 
or two or three or whatever we're clicking here   we automatically enter that it's going to 
automatically enter so that's going to be on order   load when we order load we need to know the search 
order we need to know make sure that b4 contains   a value we went over that just briefly b4 is going 
to be based on the order id of whatever is located   in d8 the user will enter an order id here it will 
determine the row of that order if it's not empty   we know it is an accurate ordered number we can 
then take that order number and place it directly   inside b5 and that's what we're going to do here 
so we certainly want to make sure if b6 is empty   we know it's incorrect b6 of course here is our 
order row we want to make sure that however when   we make that change we do that all in the change 
event orders we went over that already remember on   d8 change we're taking care of that we're placing 
that that order row here then we're running the   macro order load so we went over that already 
now we're ready just to make sure that b6 doesn't   contain it must contain a row must contain a row 
associated with that if it is empty we got to let   the user know otherwise we're going to extract 
that and put that into a variable here order row   i want to set the order load to true as we clear 
out all the contents of the fields to making   sure that everything gets cleared out and we're 
going to turn off application screen updating   we're going to again load all the information 
using data mapping right just as we did before   bringing in all the data from the database because 
we have an order column and we have an order row   so we can bring in all the data we're setting the 
order type into a variable and we're also going to   delete the validation just like we did before and 
we're going to update all remember also on order   right also we load order i want to make sure that 
those are income accounts i want to make sure that   the status here are all the statuses for those 
for only for invoices and i want to make sure that   only this is associated with a customer right and 
not vendors because when we load a purchase order   things will be very different right so or work 
order things are going to be different okay so we   want to make sure to do that okay because purchase 
orders of course are going to have vendors and   different accounts so we want to make sure just 
like we did before deleting the validation and   using select case to update all of the validation 
except we're not putting any defaults in here   because it's not a new one great so we're going to 
set the status type we're also setting the status   type here and then what we're going to do is 
we're simply going to update the status type just   as we did here now what we have we have additional 
loading items right we want to load all the items   i need to load all the items associated with this 
order all the items associated with order 5 and   that's going to come directly from our order items 
database here so what we want to do is create a   filter based on some criteria now that criteria is 
going to based on order five right we know inside   the orders b5 is going to take our order id number 
so we can use this formula to create a criteria   then we only want to extract those items with 
those specific uh on order five and i want to   get the item description quantity amount row order 
row and row bring all those in here and we do that   with an advanced filter okay so first of all we 
need to know the last item of the order the last   order item database i wonder the last item here 
right because we're going to create an advanced   filter so we need to know the last order item is 

So once we do that we're going to put that   into a variable if it's less than four that means 
we don't have any items we're going to set that   advanced filter based on the order items database 
a3 through l so we're going to set all the way to   l right we're going to use criteria n2 through 
n3 and we're going to have those results appear   directly from r3 r2 through w2 so bringing those 
results in having our criteria and 2 then 3 and   those results from r2 through w2 making sure 
that that's correct r2 to w3 good then what   we want to do is we want to determine the last 
row of results using column r right so that last   results row based on column r of that database 
and if it's less than three that means we have no   database items we're going to skip and go to here 
if we do have results we're going to loop through   those results from three to the last results row 
we want to set that item row based on the order   items database v that order item row that order in 
row is very important because we need to know what   row to place it row 10 row 11 church of where are 
we going to place the values here here here okay   so we need to know that okay continuing on what 
i want to do is i want to take all the names item   name description quantity and amount and i want 
to place them directly so we can use that so e   through h is simply equal to here r through u so 
that's all we do through this line of code rfu   and then i want to add that database in that 
database here that database row must go into   column j here normally it's hidden okay so we're 
going to place that there because we have to know   it's coming from there these are these are hidden 
right that's why these don't show up remember   these are hidden right using that just so you 
don't know why they're showing some this is custom   more number formats they should be hidden with 
these three semicolons it's going to hide them   these are not hidden so okay perfect so we know 
that bringing those bringing that item database   roads we're going to set our order load back 
to false and then we're going to turn on screen   updating perfect great so we're almost done with 
these orders we've done a lot so i want to be able   to print so we've covered add new copy to we've 
covered save and update i want to print the order   that's simply all we've already done is just set 
the print we know that we have a very fixed print   range right we know it's going to come all the way 
from here so this is our print range right here   these cells so when we do place layout we can just 
print set that primary it's going to be fixed it   won't be changing because we have a very specific 
invoice we did do a training if you want to see   a few weeks ago how we can do unlimited rows for 
unlimited items on a multi-page invoice that was   kind of helpful so we've got a printer so all i 
need to do with the macro is just simply print   it out so orders print out and then just add in 
these variables right we know we know what we have   we remove the comment and then add it in we 
can see all that it's front page two page   copies preview add to printer print to file false 
and then we ignore print areas false we want to   make sure that we are keeping those print areas we 
want to email it i do want to email you saw that   briefly earlier if i want to click email i want to 
email that order and then what it's going to do is   going to create a pdf and then automatically email 
that order so that's going to help print it out   it's going to put it into a pdf and automatically 
email that order as you can see down here this   purchase order created inside an email so what we 
want to do is i want to set that email to whoever   where our vendor is or our customer i want to 
set some default subject i want to create a pdf   using that relatively simple so we're going to go 
over that so first of all the file name i need to   set a file name where are we going to save that 
pdf it's going to be based on the current workbook   path using this workbook path i'm going to add a 
backslash and along with b2 right i want to add in   what type of file right i want if we noticed in 
that pdf that we just created it's going to be   called had a very specific name and the name 
is going to be that file name they'll afford   it so is it an invoice is it a purchase order 
right so when we create that pdf we see that   a very specific pdf called purchase order and 
then underscore five right so i want to make   sure that that b2 that order type along with that 
purchase order get inside that name make sure that   file name so we're going to add b2 underscore 
b5 that is going to give us a name and then   a dot pdf if for some reason that file exists 
it's going to create an error we try to create   a new pdf so we're going to kill it delete that 
file if it already exists we're then going to use   export as pdf file type pdf and so we're going to 
export it out into the pdf using that file path we   can export it out right using the pdf using that 
file name we do not if we want to display that   pdf we would mark this to true we're going to set 
that outlook application we've already allocated   alec mail we're going to use late binding those 
two right there have been defined as objects here   so we're going to use late binding we're defining 
the outlook object here outlook app as an object   with the outlook application i want to create that 
email that initial email select that outlook email   to create item this is going to create that 
email once i create that email i'm going to   set that 2 to whatever's in b10 how do we know 
what's in b10 b10 is the email we're going to   use that i've used that i want to use either a 
vendor or of course i want to base it on a work   order if actually if it's a workload i'd like 
to email it to the employee right so that would   be kind of nice if it's a work order and we've 
selected an employee i want to email it to the   employee think like lisa let's say i want to lisa 
so generally we'd probably email a work order to   an employee however if it's a customer purchase 
order or work order i want to email it to a   customer or vendor so we need a little bit of a 
formula to help us out if the work order type is   a work order type why don't we index whatever 
is located in i5 that's our employee name   and i'm going to index our email we've got a name 
range called emails that's for all the emails for   our name range and we're going to look for it in 
row based it on the match in i5 so this is going   to extract the employee email however if it's 
not a work order if it is a estimate invoice   or a purchase order i want to email it to whoever 
we've put inside b8 so in this case we're going to   index email based on whatever's located in b8 
minus 3 because we already have the name row in   here right we've already if we set a customer here 
we already have the name right here so that way we   can extract if it's a proposal we know let's go 
ahead and put in a customer name here that we   can extract the email from your app so we can get 
the email so based on the form type we're going to   either the email is going to be from the employee 
or from that so we're going to set that inside the   two whatever is located in b10 we're going to set 
that as the customer actually should put that as   or employee email could be all throughout either 
one of those okay we're gonna attach that file   name we've already created that right we've we've 
created that pdf file right here so all we need   to do is add a chat and we add a dynamic subject 
please see the attached we want to know if it's   an invoice the order type right here right and 
also we want to know the whatever's in b5 and i   want to make sure that they have that number 
that id number here and then nothing for the   body and then we're just going to display it if 
we want to send it right away we'll use dot send   okay it's cleaning up i'm going to set that up 
mail nothing okay the last particular macro for   this particular module then we're going to be 
did dashboards and i think that's going to be it   delete right we want to make sure that we're 
going to delete the order if b6 is empty right b6   is empty like in this case because they have not 
saved it so if i decide to delete it and i say yes   it's just going to simply clear the clear it out 
and go back to the default order which is invoice   however if it has been saved before we know that 
b6 is going to contain a value we can extract an   order row directly from here so we know if order 
row is going to go to not saved it's going to skip   all the way down here go to not save however if 
it has been saved before we're going to take that   order row and put that into a variable here then 
what we're going to do is we're going to delete   that from our orders database using order row 
and order row entire row delete we're deleting   it there i also want to set the order type 
based on b2 because that's very important right   i need to know how we're going to deal and update 
that so with the order items database we also need   to delete all the rows associated with that so 
if i just decide to delete this i need to loop   through these i need to extract get these orders 
determine those database rows and extract them or   run an advanced filter and do that as well so we 
can do that with an advanced filter meaning i'm   going to run an advanced filter i want to know all 
of the rows associated with this one i want to run   an advanced filter to do that inside the ordering 
database so we're going to take that order id i   want to know all the items associated with that so 
that i can delete them but i want to delete them   in reverse order meaning i want to start in row 
5 and go to row 4.

I always want the highest row   first so we do need to sort them so we're going 
to run that advanced filter getting all the rows   right determining the last row then if the last 
row is less than three go to node database items   okay so we don't need that however if the last row 
actually probably does the last row is less than   four then skip sort right because we only 
if there's one item we don't need to sort it   so let's do that if the last row is less than four 
right because that means there's just one row of   data then let's go to no sort go to no sort okay i 
like that better so we'll skip the sort here then   no sort okay okay so we can do that there inside 
no store we don't need to sort it there based on   that there's just one row if there's more than one 
row we're going to run sort based on that we're   going to clear any sort we're going to base it on 
w3 right i want to know i want to reverse these   i want the highest row first here when i delete 
them i always want to delete the highest row first   so sorting them based on w3 and i want to make 
sure they're sorted descending the highest first   we're going to base it the range entire range sort 
that entire range from r3 to w it's going to sort   all this entire range once we have that then i can 
then delete the rows the result row equals 3 to   last results row i'm going to set that order item 
row based on whatever is located in w then i know   what row to delete so we're going to set set this 
delete row we're going to delete that entire row   very easily enough so now we have that inside 
there we probably don't need this right here   because we're already set on that one good so now 
continuing on so now all we need to do is just   let the user know that it's been saved and run a 
brand new order that's it so that's all we need so   if i decide i'm going to delete this order right 
here we can do that right now inside the orders   so all we need to do is just let's say enter one 
of the older orders that we just created and then   delete the order and i sure want to delete there 
yes purchase order has been deleted we got the   message and now it goes to the default okay 
great great so that covers the orders right   this we don't need here don't need to test that 
very good we also have a very cool home screen   then we're going to get to the dashboard if you 
take a look at this the home screen relatively   simple all we have is just a few macros that are 
going to help us out clicking on the application   macros here we've got go to sheet this is going 
to help us so how do we do that right so for   these few all we need to do is just name the shape 
whatever the name of the sheep is so if you see   this transactions or you see this called dashboard 
and then this is called items and this is called   admin and this is called orders so if the user 
clicks on any one of these all we need to do is go   to the sheet based on whatever the shape name is 
and then another one we have home same thing here   home right so the same macro can be used for all 
those and let's say macro is called menu go to   sheet so all we need to do is take the name of the 
shape and activate the sheet associated with that   as long as the name of the shapes are exactly 
the same as the name of the sheet the shape is   exactly the same so that's all we have to do just 
one sheet will do just that for us so relatively   simple now it's slightly different we went over 
this already outside the home remember we went   over these these eventually we'll do a little 
bit differently this one we're actually going to   go to the sheet so we create a brand new one 
remember this one we've already shown you on   this let's just take a look at that under the 
home remember this one the macro that's assigned   to this let's go assign the macro off the screen 
here remember that's called order new remember   already knew we went over that one remember just 
inside the order macros right here or order sheet   macros here order macros here all the way at the 
top order new we already went to inside the order   new remember if it's a home screen we did all 
this based on whether they're coming from the home   screen so we already covered those that's going to 
create the automatically the new so that's it for   the main menu very very simple just creating these 
shortcuts okay cool but what about the dashboard   let's try to get to the dashboard this is a really 
really cool on our last screen that we're going to   go over today in this marathon training right so 
we've got a profit and loss of period profit and   loss based on the period that they've selected 
right and we also i don't think we have much i   don't have much very very much data in here a 
little bit but uh we have this year as we see   we've created additional data here so how do we 
create this really cool date range well the first   thing is we do understand that we have this date 
range here that we've created in name range called   date range so it's called date range and then 
what i want to do is basically when a user makes a   change to this date wrench here let's go ahead and 
pull up our headings for now and what i want to do   is if they make a change to e3 i want to then add 
that from date to h3 and i want to add that today   to k3 and of course that's going to be based on 
whatever the user has set here and here okay so to   do that we make sure that's going to be a change 
event based on change event on e3 so let's take a   look at that inside our dashboard here we're going 
to make a change to e3 okay so if there's been a   change what i want to do is i want to dimension 
the date range as a string and then range row is   long and then what i want to do is i want to set a 
a1 to true what this is going to do is just set us   a temporary field right here called aa1 i want 
to set the date range to true the reason is is   that i need to differentiate between when a user 
makes a change right if they make a change to here   that's one type of change or maybe the user 
makes a change to here and we can set so there's   two different types of changes one here and one 
here and i want to differentiate those two types   of changes and i'm going to use this field true 
or false a1 to differentiate those so i'm going   to set this to true and then back to false when 
the user makes a change to e3 so we're going to   do that for right here a1 equals true we're just 
going to set it back to false when we're finished   so we're going to set the date range to the target 
value and basically what i want to do is i want to   see what range they've chosen right if i take a 
look inside here inside the code i want to know   what range this year i want to know what row is 
found on i want to know that that's found on row   17.

If i know it's found on row 17 i know that q 
17 is the front date and i also know that s 17 is   the two date so that's going to help us so we're 
going to extract that row using the find function   and we're going to base it based on the date range 
right we're going to look for the date range that   they've entered and we're going to extract the 
row if it's found then a date range is not going   to be 0. then again all we need to do is inside 
h3 place whatever's in q and the range row that's   the from date and then s from the admin screen 
that's the two day i'm going to place that in k3   then calculate that just to make sure then you're 
going to set a1 back to false and then we're going   to run the macro that refreshes the dashboard that 
dashboard is going to help us out that refresh   then also if they make any change to either one of 
these if they make a change to either h3 or k3 i   also want to refresh the dashboard too that's 
going to come here if the user makes a change   to h3 or k3 and i want to make sure that h3 and k3 
are not blank right checking to make sure they're   not playing then i also want to make sure that aa1 
is false right because that kind of change i don't   want to run it twice so making sure we're going to 
refresh the dashboard so basically on any kind of   a change whether it's an individual date change or 
in a date range change we want to update the data   that's going to update this data so how are we 
going to do that so let's take a look at some of   the macros non-net data refresh so this dashboard 
macros here all the way up here we just have a   single macro here it is let's see right here i 
want to make sure we show this dashboard refresh   first of all we notice that we have some pictures 
associated these pictures are associated with   this i want to know the top 10 products to order 
right i want to know what items we're running low   on i know that we need 14 coffee right we're 
running low on this and we because we have a   minimum set how do i know that well if i take 
a look inside our items database and we're also   going to focus on the transaction to calculate 
the r under remember we still have to go over   this formula right here the quantity on hands 
i want to know how many order items to order so   if we look inside that coffee cream of beans here 
coffee let's beans crema i want to know that where   our minimum quantity is 30 but we only have 16 on 
hand so we need we need we're short 14 right so if   i take a look at the dashboard i know we need to 
order four at least 14 of them we're short that so   that's the number one we're order we're running 
short on that so we know we've got to run that   so how do we know that well again the first 
thing what we want to do is we want to make sure   that we're going to run advanced filters so we 
know all the details i want to clear all these   pictures out that's the first thing we're going 
to do inside this macro so these items are always   called item pictures so if we take a look say 
it says i didn't pick 12.

I didn't pick 13 here   and 11 they're going to reverse order 10 9 but 
they also contain the word item picture so that   means we can delete any item picture here so we're 
going to loop through inside the dashboard all the   shapes any picture that contains item picture 
we're going to delete it now what i want to do   is i want to get the data for the profit and 
loss we'll go in order on this okay so profit   and loss i want to get the data that's going to 
contain this profit and loss i want to know all   the income all the cost of goods sold the gross 
profit and that and that's going to come directly   from our orders database right i want to extract 
all that and i don't want to put it inside here   so we're going to have that dashboard results i 
want to get the profit and loss so the best way   to do that is run an advanced filter based on the 
dates the user has selected if we take a look back   in our dashboard i've given these named ranges we 
see that this is called from date the name range   here and we see that this is called to date the 
named range here right so knowing that's going   to help us create an advanced filter so i want to 
know all the orders from this date to this date   right but i don't want to know just all the orders 
i really want to focus really on purchase orders   and i want to focus on invoices because those 
are all parts and i also want to know all the   transactions right so we also want to know all the 
income and expenses from here so we got to combine   two rows of data here so let's take a look at that 
so the first thing what we're going to do is focus   on the orders database i want to clear all the 
existing contents we saw some data right here   inside our orders database we saw some data here 
so what i'm going to do is i'm going to clear all   the data from here aa3 through ae so here's what 
we're going to do we're going to clear all that   data from right here then we're going to determine 
the last row of the orders the last one is less   than 4 it means we have no orders what i'm going 
to do is run that advanced filter but i want to   copy down the balance formula i want to know what 
balances right that's very important i'm going to   copy down the balance formula here's the formula 
right and we're going to copy this down to all the   last rows just bringing it all the way down to 
the last row so once we've done that it's going   to come directly from m2 that formula m4 the last 
one bringing down the balance formula then we're   ready to run our advanced filter it's going to 
call from a3 through l all of our orders here in   order to list a3 all the way through l right and 
then i don't need the balances in that that's just   health card and i want to use it from criteria the 
order date right i want two order dates it must be   greater than or equal to from date and it must 
be less than or equal to two date greater than or   equal to from date less than it goes to date and 
the reason i i have two there is because i want   both the invoices and purchase orders i want to 
include both of these right because the invoices   are where our income is and our purchase orders 
are where our costs are associated so i want both   of those if we're focused on our profit we will 
need to know both income and our cost so invoice   contains our income purchase orders container 
costs both are very important so our criteria   is going to be s2 through u4 s2 through u4 all 
that and i want those results to come directly   inside here aa2 through ad so our results are 
going to come a2 through ad2 then we're going to   determine the last results row aa right i want 
the last results row if we take a look at this   here we're going to get our order type is here 
invoice all the way to here actually it doesn't   include this so this is we'll delete it so that's 
going to give us the results i'll show you how the   rest of that comes right and that's important 
as well but i'll show you that in this step so   we're going to bring in all the information here 
so that's going to bring in get us our last or if   the last results less than three we're going to 
go to no orders then what i want to do is if the   last results row is less than three then put the 
last results equal to two right i just want to set   the last results row it's either going to be 12 
if there's no data we're just going to set it to   two or three was it two or three it's going to set 
it to 2 because i'm going to add on top of that   don't worry about that i'm going to set it to 2 or 
the last results rows 2 right because there's no   data so what i want to do now is i know that our 
invoice we have all of our invoice and purchases   but in this application we also have transactions 
transactions include expenses and income as well   right we've got income and we've got expenses 
so what i want to do is i also want to bring in   all the information through our transaction 
database based on those dates right   so i want to know all of the transactions based 
on those same dates so we have criteria greater   than or equal to the from date and i also want to 
less than or equal to the front i want to bring in   all of that information based on the transaction 
dates i want to have those results come here and   i want to bring in also those results i want to 
bring it on top of that into our orders database   here and have that appear down here so that way we 
have all the information here so actually bringing   it down to right here and then bringing the 
type of expense or income would show up here so   that's what we're going to do so you see as soon 
as i refresh this dashboard just by clicking here   we go back into the orders database we now see 
that we have all the additional information so   we're going to also go through the transactions 
we're going to focus on the transactions right   so we also want from our orders we want all 
of our invoices and all of our purchase orders   and from our transactions we also want that so the 
last row based on our transaction database if it's   less than four go to no transaction data so we're 
going to run an advanced filter from a3 through   f inside our transactions database here a3 through 
f right i also want f is fine we don't need the   memo in here our criteria is going to be right 
here we can also use this v2 through the w3 that's   sufficient enough right two dates i want to lessen 
this so that's always through three it's fine here   so that's all we need to do inside here where are 
we and w3 that's sufficient enough not the fourth   one okay so it's our track i want our results to 
go into aa2 through ad2 our results must go here   a2 3d and i want all the results to come down here 
i want the type the date the amount and account   notice it's very very similar to our orders 
database here right order type order date the   amount and the account right also the same right 
because i want it almost the same accounts are   going to be the same right in here so we're going 
to extract the transaction database i'm going to   bring it right down to here so can determine the 
last transaction row right here the last row is   going to be based on column a a i want to know 
the last transaction row in this case it's 10.   so i want to take all this information and i want 
to basically copy it over and i want to determine   the last row of our order database which was right 
here and i want to paste it right in here just   paste those values in here maybe one above one 
above here so basically i want to paste it all   above here that's what i want to do inside here 
so that's exactly what we're going to be doing   so once we determine our last row of our expenses 
we can then bring it all information so our orders   database ae 3 formula oh i forgot i want to bring 
down the formula right here so so here here we   combine the two of them almost missed a line here 
we're combining them our orders database a a and   the last results are a plus one through a d in 
the last results row plus one plus the transaction   basically all we're doing with this line of code 
is taking all the results from our transaction   database right here all those results and simply 
bringing them over to the next available row right   here so just bringing them all over here then what 
we're going to do now we have all this data then   what i want to do is i also want to know not only 
do you know the account i want to know the account   type is it an income account is it a cost of 
goods sold or is it in the other expense account   and so we can use a formula to determine that 
right here so all i need to do is look it up so   what i'm going to do is i'm going to take a look 
at this account here and using a formula determine   that count so that formula is i'm going to use 
isair i want to check is it an income account   the first thing we can do is run the match and 
if it returns an error we know it's not an income   account but if it's false we know it is an income 
account so we're going to use the match if there's   no error we know it is an income account so we're 
going to because i'm using a mass the account's   income i'm matching whatever's in ad3 if it's 
false we're going to setting up if it is if there   is an error then what we're going to be doing is 
we're going to index the accounts expense accounts   types remember we had a named range called 
expense accounts types and that was right here   insider expense i want to basically index this 
i'm going to look up for the expense i'm going to   match it i'm going to determine what row and i'm 
going to return whether it's a cost of goods sold   or other expense and we're going to bring that 
directly inside here and i'm going to copy that   all the way down here otherwise if there's an air 
show empty so what that's going to do is going to   return the count type bring it all the way down 
here once i know the account tab i can then create   a summary based on that that's all we're going to 
do here so great so the last row here and then all   we're going to be doing is bringing down that 
formula getting the last row basically in a a   getting that last row bringing down that formula 
once i know that formula our excel formulas can   take over so we have our dashboard panel all 
i need to do is create total income cost of   goods sold and then simply do a sumif so what 
we're going to do is we're going to summing   all these columns based on the column types so 
if it's an income based on the type of income   i want to sum it here if it's a cost of goods sold 
i'm going to put it here i want to know the gross   profit which is simply the total income minus 
the total cost of goods sold and i also want to   know the other expenses we're going to use some if 
other expenses then the net profit which is simply   the gross profit minus that now if we go into our 
dashboard we see that we just need to create some   links so i just created some simple text boxes to 
link actually to those fields so if we take a look   here we see that we have a text box right here so 
this text box here is located orders db h3 h4 so   these are all text boxes simply linked to that 
background let's move that nice little picture   back up here not this one this one then i just add 
a little bit of a picture although you can almost   not see it and a coffee picture here which i kind 
of like so we have this so these are all just   text boxes simply linked to our to these fields 
right here all the way so i definitely link them   that's it that's how we do our profit and 
loss okay continuing on with the code let's   focus on our get transaction for the actual we did 
that top 10 needed items so we've covered data for   the transact for the profit and loss we see how we 
did that now we're going to focus for the top 10   needed items remember in the dashboard i created 
this i want to know the top 10 needed items so i   need some code to help us out with that so that 
we can determine all the items that we needed so   how are we going to do that we're going to focus 
on the items database so that's going to take   our focus right here and again we've created some 
criteria and what i want to know is i want to know   all of the items that are inventory right remember 
we don't want non-inventory items we only want   inventory items and i want to know where the 
minimum quantity is greater than zero right if   you set a minimum quantity to zero then we don't 
need it but i only want to know those which the   minimum quality is greater than zero and i want 
to return all those items and the information   here so i want to know of the item i want to know 
the picture the minimum quantity the item name   i want those rules to come in here then using 
formulas we're going to calculate the quantity   on hand and the quantity needed so let's take a 
quick look inside the vba here so the first thing   what we're going to do is we're going to clear any 
previous results that's burned all the way from t3   through x clearing all these previous results here 
we don't need those results there then what we're   going to do is determine the last row if it's less 
than 4 exit right i'm going to run an advanced   filter just as i'd mentioned to you q2 through 
r those three those are going to be our criteria   right making sure that we have an item type of 
inventory and a minimum quantity greater than zero   q2 through r3 make sure that's set to r3 what the 
results come through t2 through v2 all of those   results right here then we're going to focus on 
quantity on hand and both quantity needed once we   determine the last row based on column t if it's 
less than three we'll go to no items then what we   want to do is we want to create a formula now if 
created formula we need to know the quantity on   hand essentially the quantity on hand is all the 
items that we have ordered minus all the items   that we have sold right that's essentially it but 
what we want to do is i want to make sure that we   order so i want to know the order items and i want 
to base it on the status right i need to know the   status right if we've just ordered them we don't 
necessarily know that they're in stock so we want   to sum all the order items based on the quantity 
based on the item name right so this specific and   i want to know the order type if it's a purchase 
order only if it's a purchase order and the status   equals pa what does that mean basically what i 
want to know is i want to know all of the items   that we ordered through purchase order but i only 
want to know those statuses where we receive right   i want to know this or this right p8 or p9 if it's 
a purchase order and it's pending those items are   not in our stock if it's ordered it's not in our 
stock if it's shipped it's not in our stock right   i only want to know those purchase orders 
where they have this status received or unpaid   or received paid because those two statuses 
are the only ones in which we actually have   the inventory right so i only want to sum purchase 
orders with those particular statuses either one   of these two either p8 or p9 so i'm going to take 
all the purchase orders with p8 add in those and   all the purchase orders with p9 i'm going to add 
those in only those two so that's what we're going   to focus on so only those with status of p8 and 
only those with status of p9 so i'm going to add   in all those purchase orders and then i'm going to 
simply subtract all of the invoices in which that   particular item came up so we're subtracting all 
the quantities right order items quantity those   are the order and the quantity we're subtracting 
those from the invoices so again every item every   specific item that we have ordered that is either 
received and not billed or received in stock   minus any items that we've been invoiced 
that's going to get us our quantity on hand   now how do we know the quantity needed well the 
quantity needed if we have a minimum quality of   5 and we only have 3 in stock we know we need 2. 
so once i copy down these formulas i then want to   automatically sort it and the graph isn't going to 
actually be opposite so when i create a graph it's   going to start at the bottom and go to the top and 
that should be there so i'm sorting it based on   the lowest to highest so that way inside the graph 
itself inside our dashboard it's going to show   highest to lowest so that's what we're going to 
do inside the code so once we have our results we   copied down our formulas our formula is located in 
w1 and x1 here's our two formulas located in the   adams database w1 and x1 i'm going to bring down 
these formulas that's going to set automatically   rows but i'm really really only focused on the 
top 10.

So once i sort them we're going to take   care of that as well so how do we do that moving 
on inside the code right i want to make sure that   when we bring down those formulas and check to 
make sure that we actually have last row if it's   less than four we don't need to sort them and then 
i want to set the last row the last row is going   to be based on 12. so we should probably move that 
down here but for now we'll keep it up so we also   want to sort the fields right i want to sort all 
of the fields and i want to make sure that they're   sorted beta based on x3 because why is that i want 
to know the top ones right what is going to be the   top ones inside our digits i want to know x3 right 
what is the most quantity we needed i want to put   it down here so we're going to sort them based 
on x3 i want ascending right i want the lowest   first because that's with the dashboard then what 
i want to show is the last results row i want to   run that sort based on t3 through x right 
that's what we really want to focus on here   t3 through x so when i pull up the item database i 
want to sort all this information here and i want   to know the last so we can that's it once we 
create our graph we're going to create a graph   based on the quantity needed so that's what we're 
going to do inside the code we continue on here   and we're going to add the pictures once we sort 
it i want to add the pictures we're going to set   that picture folder we know that in picture folder 
making sure that we have a correct picture folder   i want to set an initial left position and not 
top position i want to place all the folders   inside the dashboard i want to place this i 
will be making a few adjustments because it's   not quite exactly it's almost perfect but i want 
to make some adjustments based on the last row so   making some adjustments by the time you get it 
okay so what i want to do is i want to set the   last position i want to create these pictures and 
have these pictures appear next to the items we   since we have the pictures models we're using i'm 
going to set the initial left position based on   n6 and i'm going to move it 48 pixels to the 
right top position based on n6 and 4 pixels   up we're going to loop through all the results 
row from the last results row from the bottom   to the top right i want to go from the bottom to 
the top why do i want to do that because i want   to make sure that we're going to the highest ones 
possible right the last one's possible so we're   going to start at the last row the last results 
was less than 12 we only want 12 results right   we don't want greater than 12 we're going to set 
the minimum to 10 so it's going to go from 12 to 2   setting our 12 to 3 setting the maximum of 10. 
what we're going to be doing is we're going to run   a loop right then we're going to set the picture 
path right i want to set that full picture path   i know inside the item database if i've got the 
picture file name and i've got the folder i can   have the full picture path combined so that 
picture path is simply going to be the picture   folder with the backslash based on whatever is in 
column t we're going to insert that picture we're   going to give it a specific name called itempick 
and the result row then what we're going to do is   we're going to place that picture on the top 
position after we've inserted it we're going   to set the left position we're going to bring it 
over based on the name right i want that based on   the name if i know the length of the name right 
this item name right some of the shorter names   how i want to put the picture closer to them if we 
take a look in the dashboard we see that this name   is a lot less characters so i want to bring that 
picture closer over however this one here has got   a lot of characters i want to keep it on the left 
so we can use this formula to determine that 26   minus the length of the name multiplied times 
4.5 and basically that just space out the left   position accordingly so that we can space it 
out so that it stays to the left of the name   since we're not basing it on any cells the height 
setting a specific height of 25 and a width of 25   and then we're going to increment the top 
position plus 31 so that's going to bring it down   bring that picture down along with the 
ice so it's going to keep going down   that's how we're going to supply the pictures then 
all we need to do is just simply create this graph   based on the data so if we select the data we 
see that we've got a graph based on the data   that's supplied here we're going to be showing 
the quantity needed and then all i want to show   is the items associated so we just created a bar 
graph to show those very simple items and that's   just created i want to show the labels here so 
that's all we've done here great next up what   else do we have to do well we also want to get the 
sales data based on a range right i want to know   this sales data and i have to get that based on a 
range and then we're going to create a pivot chart   based on that so if we take a look at that we're 
going to focus on the orders database because   that's going to get us our sales data we're 
going to run again we're going to clear the   existing data we've got some more data going back 
inside the orders database here and we're going to   see here we've got some sales data so let's take 
a look inside we're going to be clearing out ak3   through al999 so ak3 through a i want to clear 
all the sales data right i want to bring it in   here so i want to know all of it bringing that 
based on whatever sales that we've created so but   we don't want to create i don't want all the sales 
data i want to base it on very specific criteria   and what is that criteria is going to be based 
on s2 through u3 let's look at that criteria s2   through u3 i only want order types of invoice and 
i only want based on the very specific dates that   we've created so that's what i want to return only 
invoices not purchase orders and i want to bring   that data directly inside here so that's what 
we're going to do here the last row i'm going   to run our advanced filter s2 through u3 bringing 
that data over once we have that data i can then   create a pivot chart based on this so i've created 
that and we create a pivot chart based on that   so as we bring this information down we have a 
dynamic pivot chart based on that and then i just   created a graph based on that so if we take a look 
inside the dashboard and we look at the data based   on that we see that the data is based on this 
total data based on this information here this   mini accounting application and we've based it on 
all the data here inside our orders database the   sales pivot data sales data pivot that is the same 
data that's correctly right here the sales pivot   data so if you take a look at the dime we see that 
the sales data is directly based on here so we   just created a chart based on that just a little 
bit of a line chart not given in any backgrounds   and just giving a little bit more information here 
adding some fonts and things like that relatively   simple on that nothing too famous on that 
continuing on i also want to get the outstanding   invoice data now that outstanding invoice data 
we're going to create this very cool create this   cool tree map for both outstanding invoices and 
outstanding bill payments but first we need to get   the data so that data that we can use of course 
inside an advanced filter we're going to have that   data the same we're going to use our criterias aq2 
through a s how do we know that let's take a look   inside the orders database here and we're going 
to be looking inside our aq so we have outstanding   invoices criteria certainly we want to know it's 
an invoice we also want to make sure the due date   is greater than less than today right it has to be 
less than today and i also want to make sure the   balance is greater than zero so i want to know 
all the invoices with that criteria and i want   that those rules to come directly here and bring 
those inside here so it's going to bring all the   data inside here then what i want to do is i want 
to create a tree map based on that so if i decide   that we're going to insert right here tree 
map i just want to insert a tree map here it's   going to base that on here and then we're going to 
customize that tree map based on that data here so   we can select that data here and then of course 
we can make the updates right if we don't want   to include the particular order id we can just 
include the name and dip and we can there we can   create the name so there we've also done that here 
just this little tree map here and then i can just   simply move the chart and move it to a different 
that's just what i did here i moved that chart   so if we take a look inside the dashboard and i 
just updated the colors we can update the color to   this blue which is a little bit of our theme and 
then we can customize it here if we decide we're   only going to add data labels here we don't want 
a chart title we don't want a legend just data   labels and then i've increased the font made it 
bold and white so that's pretty much it i've done   the same thing with outstanding bill payments 
outstanding build payments exactly the same i've   created however differently differently this one 
is a little bit different if we take a look inside   our order database here if we move it over here 
this time i want outstanding purchase criteria i   want to know only those purchase orders i want to 
know a due date is less than the current day and i   want to know balance greater so those are bills 
that we owe and i wanted those results to come   right in here and then i've created another tree 
map directly just like i did there and put it here   wow what an epic training for those of you 
who stay till the end congratulations thank   you for sticking with us to the end let me 
know in the comments that you stay till the   end i want to hear from you what did we learn in 
this training wonder how to create an incredible   mini accounting application complete with a home 
screen where we could quickly go to any screen   we also created a amazing vocabulary we could 
fully customize an application based on the end   user requirements and then have those new names 
throughout the application we learned how to   create this incredible order screen whether we're 
creating proposals work orders estimates invoices   and we showed you how we can easily quickly 
change those on just the thing to automatically   update the application and update the end users 
experience on that using conditional formatting   data validation creating orders creating inventory 
we also were able to add pictures and items   inventory and quantity on hand we would also show 
you how we can quickly and easily edit customers   employees names and also add new ones of course 
we will also show you that email print the orders   copy to orders quickly create a brand new order 
based on an existing order with our copy to   and of course we have transactions where 
we can create multiple transactions   selecting transactions quickly locating existing 
transactions based on multiple filters whether   it's date name account or amount how 
to create an incredible comprehensive   admin screen using named ranges to help us quickly 
locate default settings we have that dynamic tax   date ranges and a whole lot more and also this 
incredible dashboard where we're able to show   a dynamic date range thank you very much for 
sticking with us i appreciate your continued   support and i look forward to bringing you many 
more applications don't forget i've got a whole   ton of you if you want to support this channel 
so many great ways including the 250 workbook   pick that up i'll make sure to include this as a 
bonus inside that of course you're always welcome   download using the links down below thank you so 
much we'll see you next week have a great week

Contact Us