Forum Discussion
Sumproduct from different Sheets on multiple workbooks based on a keyword
I think where you're going with this is a 3D reference which is possible in Excel but limited to a set list of functions.
SUMPRODUCT is not among these functions. In you're setup you'll be creating external links as long as the data is not consolidated. Have you considered rolling the data up through queries then pulling the summary from there?
8 Replies
Himathetes .....In the 3 Workbooks attached,
1. Summary WB
2. Month WB
3. Customer WB
CASE 1
Consider I have a Summary sheet In which the Customer wise sales summary has to be summarised from Month Workbook for month of October for Year 2019. Consider in Month workbook, I have the Keywords i.e Month name and year in cells B1 and B2 which always remain in the same position. The data table columns also always remain in the same sheet columns. I wish to know, how with a Single Sumproduct and reference function, I can summarize the data from different Month sheets onto the summary sheet with the month value to be considered for Month name and year in the summary sheet. Also consider that as for each month new Month sheet in the workbook is added, how can 1 Sumproduct and reference function work for new sheets added in the workbook.
CASE 2
For the Same summary sheet, I wish to Summarize the data from Customer Workbook for month of Oct 2019, considering here the Key is customer name present in column B1 of every sheet of Customer Workbook. Also Consider, for each new customer added, a new Customer sheet is added.
Regards
Sagar
I think where you're going with this is a 3D reference which is possible in Excel but limited to a set list of functions.
SUMPRODUCT is not among these functions. In you're setup you'll be creating external links as long as the data is not consolidated. Have you considered rolling the data up through queries then pulling the summary from there?
