Sum Based on Sheet Name
In an earlier indirect to Sum Data article I showed how to sum Excel data in another sheet based on the sheet name in a cell. The following example is a little more dynamic where I will sum data based on the sheet name but without the quotation marks inside the Indirect formula. This provides a certain level of flexibility. In the attached file I have 4 sheets Oil, Gas, Minerals and Acid. The data on each sheet is laid out like this;
What I want the data to look like is the following, however I want to leverage on the sheet names on the left to return the tabular data above.
In order to take the Sheets and flip the around into the above summary tab the following is the Excel \ formula to achieve the this;
=INDIRECT(ADDRESS(COLUMN()-1,2,4,1,$B11))
Where B11 contains the cell with the sheet name (Oil). The following Excel file outlines the problem and shows the solution.
