Excel VBA - Invoice Generator - Microsoft Excel 2010 - Part 4

http://www.onlinepclearning.com
Excel VBA - Invoice Generator - Microsoft Excel 2010 - In this tutorial the formulas are added. Here is an overview of the application. It is a Microsoft Excel VBA project that creates an Invoice Generator that is free to use. The data is stored in 2 sheets (databases) and filter to your criteria. The purpose of this project is to help with your VBA and general Excel skills in basic application development. The template is for Excel 2010 however this application will run fine if created in Microsoft Excel 2003 /2007.
I will be adding the videos in stages. First look at the Overview video to see if this is something that you may be interested in. Please feel free to contact me if you have any suggestions or problem through the comments at the end of this blog or with the contact form in this website.VBA Excel -- Invoice Generator tutorial for Microsoft Excel 2010 / Excel 2007 / Excel 2003

---

Closed Caption:

greetings this is tripp from online pc
learning com we're going to add some
formulas to an invoice sheet this time
so that when we now are able to put in
out at different descriptions and
quantities and categories then all the
information will automatically feeling
across to the right so how do we do that
we're going to use a vlookup formula and
the the actual dynamic named range that
we're going to go to is this dynamic
named range that we created in the last
tutorial called items
look up from this column and grab the
references for code and unit price here
to the right which is quite as that and
go back we don't want to save any
changes
alright so it's going to be a bigger Cup
formula will grow up here under the
formula bar to do this equals vlookup
very simple just going to grab this
information in here and that we want
this
we just hit the f4 key twice three times
we want the actual column to be absolute
but the right not to be there comma
table right hit it three to see all
their dynamic named ranges and other
ranges and we'll choose items
ok kamar again which index column do you
want to look up its column to summer
again we want an absolute match so I put
in false 40 hit enter
alright so there it looks at up just
fine for us but there's a problem here
if we take this value out of there we're
going to get an ni value in here and
that's going to make our invoice look
really scruffy and we don't want that so
let's put the value back in here and
we're going to put a little bit of any
formula in front of this so we're going
and website is open brackets is an eye
open brackets again so if that formula
results to an end IE value another
bracket here comma then let it be zero
double brackets and double quotes comma
and then just grab this formula guy
including the two last brackets here
grab it very gently right click copy pop
it in there and paste and then we hit
enter so now when we remove the value in
here we have a blank cell put a value in
we get the cut now let's scroll and I
wish you'd just be a dragon over by
having a little cursor 10-year to copy
when it goes to that see there that that
cross the guys that you can just drag it
either
we're getting the same value because
you're referencing the same column index
we want to just change this now 23 so
pop in here and change that 23 alright
that looks fine
this would need to be formatted of
course as currency and you can format
all of those cells down there as
currency now mount is quite simple
because the amount is simply this value
here unit prize x the quantity so we
could just simply go input equals
selling here x 23 enter and we get a
value however once again if we remove
this value we're going to get an error
value area in here how we're going to
get rid of that or click on the cell
again we'll put in an it function once
again we're just going to say if open
brackets clicking here k-19
k 19 equals empty double quotes then
empty and then we'll close the brackets
up at the end here
alright so now we put a value in will
see it all start to come across all
adding up nicely and when there's
nothing in here we don't see anything on
your invoice sheet and that's exactly
what we want to happen now winter here I
want you to put some data validation we
haven't put this in yet but you can put
this in at this point it's just very
simple data validation just go into data
validation and just simply type in point
1 comma . to comment . 3 comma . for
right the way up to nine
no comment on the end and the list and
that will give you all of your
percentages so we put in a percentage in
here of twenty percent that's the
discount we want so now how do we get
this total value over here
well let's have a look at this now we
know if we multiply air amount by a
discount we're going to get the amount
that we need to take off it so really
what we want to do is multiply this by
this and then take it off that so a
formula would simply be equals this
value here then we'll put them minus
open brackets this value multiplied by
this value is bracket and and there's
twenty percent off so there's the
formula
however we remove this over here again
we're still going to get that value area
so we need to put in an if function in
here to be able to fix this app
now we think about this for a moment if
there's no value in here if there's no
value at all in there then we want this
to be blank
so that's the first thing we need to
look at and then if there is no value in
here we want this value so there's not
only on their we want this blank if
there's no value in there we want that
otherwise we want to carry out the
formula so we put in another function in
front here and we will go and give
alright so what we want to do is put in
it if function in the front here we're
going to go if Open bracket mount equals
0 then make it zero that's the first
thing we want to do but then we want to
put in another if statement so we put in
a comment put in if Open bracket and
then we're referring to this m19 sell
here if m19 equals 0 then what do we
want to do well if it equals 0 we just
simply want this value so we'll put in a
comma
so if it's true that equals the item we
want this value comma and then else will
be the rest of their statement here now
I should just have to put in a couple
more closing brackets here i think what
we got one two three on the left and
we'll put two more and there we go
ok so if we have nothing in here it will
just pick up this value if we have
nothing in here it will be blank so
there's that formula now put these
formulas up on the website for you as
well now we can simply do
with this information in here got
something we can see grab hold of these
formulas across there or should be set
up right copy them
scroll down and grab your whole range
here do that again
copy and we're going to grab this whole
range across here all the way down right
to the bottom and then we're gonna go
we're gonna get right click paste
special paste formulas and now of course
we put in a value over here just escaped
out of that we put in a value here is a
category first suits
everything's going across got that
quantity 34 and there we see
everything's working well if we put in a
discount that he percent discount here
ten percent right everything's fine so
this is all going to work fine for us so
we're just going to go down here and put
in the subtotal the subtitle is a
fantastic function equals subtitle look
at all the variations you get with
subtitle look at all count counter
max-min we just want a summit which is
this which would be the equivalent of
typing in nine and we'll put a comma and
then we'll scroll over a range which is
here is arranged reference
okay and then we just simply all we need
to do with that is closing bracket
and hit into there's a subtitle and of
course put the same formula over here so
we could do is grab hold of a subtitle
function right click and copy it and pop
it in here and in paste function
alright and it will automatically pick
up the right values that we need your
credit is in here if we put in a credit
of sight i paid 345 dollars
well then air balance jus will be one
it'll be this figure- that figure will
give us a balanced Jude so we just
simply in here would be equals this
honest that and now what I want you to
quickly do is up into this cell here
where it says customer just got a data
and data validation we want to put in a
list here of customers so we're going to
list click on the source box and it
always will hit f3 and we get customers
and this time we want we want customer
key which is just the brief list of
customers
ok and put it in so there yet now we can
put our customer into here and we're
going to put every look at formulas into
here and we'll look at their customers
so that formula
would be exactly the same as what we had
down here I won't go through it all the
guy i just put the formula and we can
move along with a tutorial so here's a
formula the dynamic named ryan we're
using is customers and you'll notice
this reference here has to be absolute
the g12 is absolute because we only
referencing one cell all the way down
here so we'll hit enter and it tells us
what we need and we just copy that again
with a copy
scroll down over here and paste in those
formulas but now they say the sign but
we just got to go in and change the
index reference 23 and of course 23 over
here hit enter and we can see it's going
to now this one will be four five and
six
so now that's working fine as well look
we just about got this whole sheet right
now remember earlier on we created a
static nine range for here just want to
reference the date in here so this will
be equals and I'd enter our invoice
number this will be equals invoice
number you enter a customer equals
customer and order number and we're
going to be transferring this . arrival
either so we need to just put all of
these in equals total which is going to
be subtitle down here
and
and a discount so what does a discount
with that discounts pretty easy to work
out isn't it's just equals basically the
difference between these two figures so
equals this- this and this a discount on
a credit it's just equals down here to
our credit and then my last one is
amount jus equals their final figure
down here and so now we have this ninth
Rangers ready to transfer over so if we
were to put a light in here we have this
formatted to add I'd 13th of the third
2013 and we can put an order number in
here which might be 12 and there we see
a complete list here of summarizing of
this that does summarize this invoice
sheet on everything now when we change a
customer here everything will change and
look up automatically the reference for
the customer come in and when we add
invoice details here we first must
select a category of course and then we
add them in
everything will add across all right so
it's all starting to work we have a
discount in our work we put in a
discount it's fine if we don't put it in
and then everything will total app so
that's a whole invoice sheet now working
for us all we can do now in the next
tutorial hopefully start adding a code
to move this dynamically across into a
databases this is tripp from online pc
letting thanks for listening once again
all these formulas will be on the
website the best thing to do with this
tutorial is going to the website because
all the videos there one under the other
and all the information is detail
between each video as well thanks for
listening