Combine/Consolidate Data Dynamically Between Seperate Excel files

http://www.TeachMsOffice.com

How to consolidate data from multiple worksheets into one worksheet. This tutorial teaches you how to use the consolidate command in excel and shows you how to add sales data for parts from two different stores. This also teaches you how to consolidate the data so that it is dynamically linked and so that when any spreadsheet is updated, the consolidated worksheet will also be updated.

Additionally, you can consolidate data from open or closed workbooks and also from different worksheets within the same workbook.

To follow along with this tutorial and to get more free tutorials, free macros, and visit the forums, check out the website:

http://www.TeachMsOffice.com

---

Closed Caption:

in this tutorial I'm going to show you
how to use the consolidate command to
combine two worksheets and the one
worksheet so if you have to work sheets
that have either similar or the same
data say sales numbers and you want to
combine those is a very easy way to do
that excel so let me lay out an example
for you
I have the spreadsheet here and this is
where I want to pull in all of the data
and i have two other spreadsheets
now these are for instance a sales
reports for the past year by quarter for
a particular office or a particular
store and I want to combine them to see
how many parts we sold in total of one
particular part 5 part numbers over here
and then their sales numbers right here
now for the two spreadsheets the part
numbers are not in the same order and
they do not contain the same parts so
one store sold different parts than the
other store and the point of saying all
that is the data is not in the same
order
so each row is not identical in terms of
the part numbers now what I need is I
need to be able to combine all of the
sales numbers for the particular parts
in one spreadsheet and also i want a
dynamic so I want this spreadsheet to
change every time one of these sales
numbers changes now this is in the past
but say we are in quarter for right now
so these numbers were being updated on a
daily or weekly basis then that would be
more important
so let me show you how to do it it's
called the consolidate feature and you
go up to the data
and then click consolidate now before I
explain this
it's a good practice to have all of the
spreadsheets open that you want to use
but you don't have to have them open and
the spreadsheet here is the one that i
want - doubted to come into so now let
me explain everything here
first you have the function window so
this is everything you can do with the
data and often times and in this case we
want some
so what the Sun is going to do it's
going to add sales numbers for
particular parts together but you can
get all these other things like average
max men etc so we can click some the
next thing is to get a reference and
this is going to be the reference on the
worksheet that you want to pull the data
in
so what sells on what worksheet do you
want
and this is where it's easier it's good
to have the other worksheets open
so what I'm going to do is click this
button here then I'm gonna go down to
select the worksheet that I want it from
and i'm going to highlight the reference
all these cells but what I want to do is
I want to highlight more than just these
rows and columns and that's weird just
these rows
that's because what if i add another
part so what's the story takes on
another part
well I want that to be accounted for so
I'm going to add a bunch of extra rows
say down to 30 then enter now once you
have the reference you have to click Add
so add will add the reference to the all
references window and now the next thing
is to get the other spreadsheet so i'm
going to go ahead and click this button
again i'm going to open my second
spreadsheet select all of this data down
to row 30
hit enter
click Add
now i have both datasets added the next
thing to note is the use label use
labels in now you have top row and left
column
you do not have to select this but if
you do not select this your data has to
be in the exact same order
so what that means all of the part
numbers have to be in the exact same
order for that to work but it's not
going to work for me because the stores
kept the part numbers in different
orders and some have more parts than the
others or different ones
so what I want to do is use labels and
top row and left column and that's going
to make sure that they all add up
correctly
the next thing is to create links to
source data now if you leave this
unchecked
you're going to have a static
consolidation so what that means you're
going to put all of the data and it's
going to be correct but if one store
updates their numbers
it will not be reflected in the
consolidated worksheet so if you create
links to the source data anytime numbers
updated it's going to be reflected so I
want to create links then click OK and
all the data is pulled in so it got part
numbers here on the left
and we have quarter one two three and
four sales
so that's how i can consolidate the
sheet so now let me explain a little bit
of what's going on here
they're going to plus signs on the left
and when you use the consolidate command
what it does is it puts numbers in here
with simple sum formulas but if you
click the plus you will see here that
each one of these is a formula
referencing that cell from the
particular worksheet and so that's how
it's going to dynamically update
everything
so let me give you an example for part a
SD o 344 quarter for it says 766 but
like i said we are we're still in
quarter for so let's update this say
this store for asd 34
they don't sell 303 but they sell 440
hit enter now I want to go back to my
consolidated sheet and it's updated
immediately
so 903 that's the benefit of
consolidating the worksheets and also of
creating the dynamic links now one other
thing i'll just quickly mention in the
upper left hand corner you have a1 and
a2 if i click - it's going to expand all
of the parts
so you can see all of their dynamic
links click one
it's going to close them
so that's a quick overview of using the
consolidate command and excel and I do
provide the spreadsheet so you can take
a look at them if you want to follow
along on the website