Hello ladies and gentlemen,
Since I had such a great experience on my last post I decided to return once again with a problem that has me stumped.
I am basically looking to build a workbook that utilizes the sumproduct function (or one that is more efficient) to combine information throughout multiple sheets into a master sheet. All sheets will look exactly alike.
There is a "Managers" tab that will hold all the managers information which is used as a reference to each manager sheet (M1, M2, etc...) with a master sheet that will be used to consolidate all the data.
Each sheet will use a vlookup function to reference a certain % of sales based on the total. I am hoping to break down my sales from each individual manager and roll them up into a total sales by each category.
Referencing sheet "M1". I would like each number in Column C to be multiplied by cell F3. I would like this same function to be done for each other manager sheet, then for all those numbers to be aggregated, and placed in the corresponding cell in the "Master" sheet.
So for instance 'Master'C6=('M1'C6*'M1'F3)+('M2'C6*'M2'F3) etc...
I feel VBA would be the most efficient way to do this because if sheets are added or deleted I don't want to have to go through and individually update the fomulas on the "master" sheet.
Also if there is a way to add a row in the "Master" sheet which would show up in each of the other manager sheets. For instance, if I wanted to add another row titled "Drinks" to the master sheet, that same row would be inserted in the manager sheets without erasing or throwing off any data pre-existing data in the individual manager sheets.
Here is the link to the workbook from google docs.
https://docs.google.com/file/d/0B8FTiSRUYbd9N3R6OHJ1UDVsa0k/edit?usp=sharing
Since I had such a great experience on my last post I decided to return once again with a problem that has me stumped.
I am basically looking to build a workbook that utilizes the sumproduct function (or one that is more efficient) to combine information throughout multiple sheets into a master sheet. All sheets will look exactly alike.
There is a "Managers" tab that will hold all the managers information which is used as a reference to each manager sheet (M1, M2, etc...) with a master sheet that will be used to consolidate all the data.
Each sheet will use a vlookup function to reference a certain % of sales based on the total. I am hoping to break down my sales from each individual manager and roll them up into a total sales by each category.
Referencing sheet "M1". I would like each number in Column C to be multiplied by cell F3. I would like this same function to be done for each other manager sheet, then for all those numbers to be aggregated, and placed in the corresponding cell in the "Master" sheet.
So for instance 'Master'C6=('M1'C6*'M1'F3)+('M2'C6*'M2'F3) etc...
I feel VBA would be the most efficient way to do this because if sheets are added or deleted I don't want to have to go through and individually update the fomulas on the "master" sheet.
Also if there is a way to add a row in the "Master" sheet which would show up in each of the other manager sheets. For instance, if I wanted to add another row titled "Drinks" to the master sheet, that same row would be inserted in the manager sheets without erasing or throwing off any data pre-existing data in the individual manager sheets.
Here is the link to the workbook from google docs.
https://docs.google.com/file/d/0B8FTiSRUYbd9N3R6OHJ1UDVsa0k/edit?usp=sharing
