I have to make a "master" table that would sum the columns from all the stores and arrange them by product name.
etc..HTML Code:
That should make a table that would sum all the products like:
HTML Code:
What would be the right way to do it? All the relative referenced calculations in the columns right of income, should also stay consistent.
You will want to create a PIVOT TABLE Report using multiple sources of data.
https://support.office.com/en-nz/art...1-e9fc8adeeeb5
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
This says: "Each range of data should be arranged in cross-tab format, with matching row and column names for items that you want to summarize together."
My row names are different, and number of rows is not the same across worksheets. Should I read further?
Matching COLUMN headers is the primary criteria. This allows Excel to merge the rows of the various tables into the same column organization.
Next, ROWS with the same name "Carrots" or "Milk" for instance on the various sheets will then be considered the same item and the data from those rows may be merged depending on what you put into the TABLE VALUES of your Pivot Table Report. If you put "Sum of Income" for instance, then you would get results in your 3rd table from post #1.
Pivot Tables will feel a little daunting at first, don't let them cow you, it's not rocket science and once you get used to how they are laid out and where all the controls are, your Excel life will improve 1000%. Pivot Tables and Pivot Charts will crunch large sets of data for you faster than anything you've ever seen before.
To create a Pivot Table from multiple sources the Pivot Table and Chart Wizard is a good tool to have in stalled. If you don't have it installed, click on the down arrow in the upper left of the Excel window (the Quick Access Toolbar), choose more commands, choose All Commands and scroll down the list to the Pivot Table Chart Wizard and select it.
This wizard leads you through the selection of the data ranges for the multi range pivot table.
You can add the same command to the ribbon by going to the Excel Options, Customize ribbon, select all commands and choose the Pivot Table Chart Wizard and add it to the ribbon where you want it.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
You could do a SUMIF across the sheets if the data structure is the same on each sheet.
What are the REAL sheet names?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I've been watching tutorials for the last 2 days, and Pivot Tables seem to be the thing that I need. Very powerful tool
Thanks for recommendation.
Thank you for giving the thumbs up, I was about to give it up after 30 mins of reading, because I felt like I will never get it (that it is too complicated for me), but after 2 days I pretty much get the logic :DOriginally Posted by JBeaucaire
Pivot Tables will feel a little daunting at first, don't let them cow you, it's not rocket science and once you get used to how they are laid out and where all the controls are, your Excel life will improve 1000%. Pivot Tables and Pivot Charts will crunch large sets of data for you faster than anything you've ever seen before.
Last edited by JBeaucaire; 05-24-2015 at 02:47 PM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks