I'm going to show you how to access account balances quickly using the
combination of pivot tables and vlookup I have a table of data here and there
are numbers of different accounts and there's amounts for each account that it
better that need to be totaled the first thing that I'm going to do is I'm going
to create a table out of US data and so what I'm going to do is I can click in
the middle of my my cells here I'm going to go to the insert tab and I'm going to
select table and I'm going to make sure I have my table has headings clicked as
well and I say ok and so now what I'm going to do with this table is I'm going
to click in the middle of this table and I'm going to go insert and I'm going to
select pivot table and so this is going to put it on another sheet which is fine
and then I'm going to say ok and so now in this pivot table the only thing I
want to have as I'm gonna have the account numbers and so I'm going to put
those in the rows and the amounts that I would like to have summed up the amounts
is what I would like to have totaled up so I'm gonna put in some amounts and you
have those next to that and in this this is a pretty straightforward pivot table
now that theory is is that if you were doing this you wouldn't have a small set
of data here you probably have hundreds of accounts and maybe even thousands and
you wouldn't want to have to scroll through them to find the one that you
need so what I'm going to do is I'm going to go up to the cell here now I
normally people have told me that if I go equals and reference this cell here
I'm going to get I'd get pivot data formula in there but I'm just getting a
four which works for me and so I'm gonna go and I'm gonna click and drag this
down so now I only have all the account numbers here and let's go in the next
cell to it and let's go equals and let's link to the data and the sales amount
and then we're gonna press Enter and then we're gonna drag this down as
well so now what I'm going to do is I'm going to select both these and I'm going
to account I'm going to name this range account totals so I'm gonna go a CC and
t OT and press enter now I'm going to create another sheet
where I can just have the account numbers and the and the count like Alan
says show up so what I'm gonna do is I'm going to make this larger so you can see
it and so in b1 I'm going to put a count and you see one I'm gonna put balances
okay now the balances can be very large so I increase this by a certain amount
here and so what I'm going to do is I'm going to create a drop-down list of all
the account numbers here so I'm going to my data tab and I'm selecting data
validation and I'm selecting lists and so the source of my list is my data that
I've gotten from the pivot table so I'm going back to sheet 2 and I'm going to
select all this data of the count numbers here and then I'm going to click
this and then I'm going to say ok ok so now if you drop this down you can access
any account number that you like so what I'm going to do here is in the other
cell is I'm going to use vlookup to figure out what the balances are so I'm
going to use the FX key and I'm going to select a vlookup and I'm gonna say ok
and so what I'd like to look up is the value that's in b2 I'm going to use the
table array which was the acct ot that I created and the column index is I want
to get the data from the second column which is the balance and that should be
fine and so we say ok and now you can drop down you could
basically if you had a phone call if you had some reprimanded Department always
look looking to see what the balance was for any account number you could
instantly bring it up now the problem with this though is you're thinking oh
that's that's nice that any particular time but what happens if the data is as
some data is entered so let me select three this one right here because I know
it's at the bottom of my table and so the balance right now is 823 so let's go
to sheet 1 and I'm going down to the bottom of this table and what I'm going
to do is I'm going to drag it down so I get the formatting there and it changes
it to 3 71 or 71 so I'm gonna change it back to 70 and then I'm going to drag
everything else down here and then I'm gonna make something dramatic so it was
823 so let me enter a thousand here and then what I'm gonna do is I am going to
drag my my date value down here I'm on the data tab so what I'm going to do is
I'm going to go and I'm gonna Fred refresh all and so now let's go look at
our pivot table and our pivot table for 3-0 is 1823 and so now let's go back to
sheet 1 I'm sorry she 3 which is our values so the account value is it's all
all up to date so it's 0 3 0 170 and it's 1823 which is the new balance so
once again you're able to pull up any balance that you want it and yet quick
as you like this is how you create a quick access balance sheet from pivot
tables and vlookup please subscribe