Excel tutorial: making an advanced budgeting or profit/loss spreadsheet

Walking through creating an advanced profit/loss (or budget) spreadsheet.
View my spreadsheets for sale on eBay under userID: DRSOY
http://www.ebay.com/itm/331328158242?ssPageName=STRK:MESELX:IT&_trksid=p3984.m1558.l2649
All spreadsheet come with full email support.
Message or email me if you have any questions!

Thanks for watching.

---

Closed Caption:

hey everybody I was looking for
something to make a video about night
came across the spreadsheet made 41 my
customers on e-bay
I've modified it a little bit to taken
in the
personal information and they'd use for
for their companies
and i'd converted into sort of a family
budget tracking spreadsheet
to my show some features and show you
some the
dynamic change ability and the number so
you had it do it yourself
so first thing I have here is
a drop down box that has the current
today's date in a
and that stores the value of the data
not a formula
update which is kinda handy then I have
a
who which family member you can pick
from a list and a category
work and expense or income is and you
can use the sub-categories
in the drop-down list change depending
on the
category it's picked so I pick utilities
gas water power show up when I pick
eating out Red Lobster
mcdonnell's an Outback show I'm
specifically tailored to be semi naked
enter some notes so if this is an
expanse
you could do it an and negative amount
all red lobsters problem on map it's a
thirty-five dollars
okay so where
is the information coming from for these
drop down lists
make sure you can see what's on screen
here
okay so on dropdownlist stab
it has the who the category
and then all of the sub-categories
listed out so let's say you want to
change
the first category to I row
I something
and then that something being costs for
for whatever
something else
and pizza
now
number one on the list is something and
the subcategory
matches where we put in there okay so
let's quickly just put in some more
information
you have to use the drop-down list for
the day
we can type it in manually arm
this is for convenience
okay now I've got a little
getting there can show you the summary
reports to the first one will summarize
by
the who on the far left column and then
by the
category and some rice per month
the totals of each category for each
individual are so the formulas in a
little while
this next up a report is a little bit
more complicated it shows only
the individuals and the costs and
sub-categories that appeared in the main
data table I'm over that how it's done a
little while
by Tom this one show you more love more
detail than the previous report
without bogging you down with too many
sub-categories
alright summer show you how I did some
nice things
first one was putting in today's date
with a dropdownlist
and your sis is not a formula it is the
value
the way to do with the formula would be
equals today
open and close friend sees and had
dinner
and that will show you the current day
every single time you opens frenchie
so the problem with that is it will
always be changing
tomorrow will be the 11th and the day
after that it was so the two of
which is in good if you trying to keep
track of historical information
such as when you hang out at mcdonnell's
so what I did was
trade a data validation
list and to get to that in Excel 2000
and high
2007 higher it's under data data
validation
and I'm referencing a named range
as the source of data so I picked list
and then made it named range so let's
back up a step on a show you how to make
the name range
data you gotta formulas and name manager
and a very creative but otherwise we
create new
so this one is today's
our date today animate the formula equal
to
dropdownlist x1
so we'll see what that is dropdownlist
x1
so this here is the formula have today
equals the course today
open/close print to see the reason
that's different than just having a
formal though his when he selected from
this drop-down list
its selecting the value above that
forming over there
so you could hand must go ahead and do
it
we could make this a little bit more
a complicated we should say maybe it'll
have the last three days you can pick
from a list
small do because today -2
today -1
and today
now we just need to change the range
that that name manager
power references looking out so
I will select the new range
still
yes
and now
we can pick from last three days
High's 1st just a little trick arm hope
that's useful
the next thing is making a arm
dynamic dynamic reference
in a name so for these drop down list
there all season names
and the refering to dropdownlist have
here
so for example if I want
this something subcategory
to have 50 items I can put 50 items in
it but I don't want to drop down list to
show
50 black choices every time I selected
so what we do there
years we make a dynamic reference
someone do that now
so what you do is you have an offset
from a starting point and you go
from that point to the left are to the
right
rather and then down 0n0
so this is a a grid coordinate system
here
so you're starting at the offset point
you're moving to the right 0 down 0
and then you're going to count the
number of entries that are
and that dropdownlist range so
the range maybe have sixty shells and a
fish for entries
the result return before and then
for the whip of this ranges want is one
call
first look at that
so in this example the starting point
would be
here at I
the beginning your list according to
move
0 to the right and zero down you want to
rain here
then you will select account hey
that's referring to this whole range and
in this case it was select three
account three as count hey is just the
number of
any occurrences that appear a blank cell
return 0
any cell with any value returned one so
count hey if this would be three one 23
and
the last is caramel one because it's one
column with
that we're looking at so when we set
that in the
data validation as a latest
you can't input the name of that named
range
and has this changes the source data
changes for the drop-down list
okay so now for
the tricky one this
dropdownlist changes depending on the
value
this dropdownlist through a little
Excel trickery that day pondered through
took me a minute but I figured out so
I'm gonna tell you how I did it
and you to post in the comments if you
have a easier way but
it seems or okay so the promises the
same
it's the same offset except in this case
her matching this occurrence
have the cell next to it
in this range of are
this dropdownlist tab and then from
there
I'm going down and doing the same thing
counting the number of occurrences
okay so maybe it'll be easier to explain
with their new scenario
so let's do 5 eating out
and the CV's I restaurants are there as
choices from drop down list
so for the offset rest are from
something
and it's gonna look for the match I love
the text 5 eating out who would go over
this far
when it finds the return the value of
the number of columns in between there
so they would be
looks like there's one column changing
the so the value be
one to 3 for 5 6
7 a 9 so in the offset initially is
gonna move
over by nine columns then from there
it's going to take
the hard Ranger this list
below that header and do the counter /a
so it's gonna say
in this range there are three values and
then it's gonna go from there and can
say
one column same things the first so the
real big difference here is
first I decided how many columns any
move over to the right based on this
I sell to the left of the cell
Casey can see if I don't have any value
here the snuff
Novaes for the drop-down when I selected
by
their property values now show
based on the selects okay so let's look
at how some the summary reports for
so first time which is going to be this
I basic summary this one works by