I have spent a few months trying to solve for trying to simplify a large amount of datasets that I do not maintain. These files are generated on a monthly basis, and the structure varies from month to month. They each have many tabs, with the month the data is for sorted in the columns, and the labels for each in the rows. These are updated each month, and the numbers change due to adjusted forecasts.
My solution to work with these has been to bring the data into a joined file each month a new fileset is generated. I have a file where each fileset is represented in its own worksheet, and all data is in the same 2-d array for each sheet.
In the columns to the left, I then "tag" each row of significance with various dimensions of what the data represents (region, person, etc). These columns are always in the same place, but the location of the tags varies month over month.
Additionally, the row across the top has the month being forecasted for (all months from Jan-16 through Dec-17 in this case).
What I am trying to do:
I want to have a sheet that is able to look at each sheet of data, and total all of the cells that match both the vertical and horizontal criteria.
-The data has text in some of the cells. I never "need" the text, but some of my efforts have been thrown off due to this.
-The data has blanks in some of the cells. Some of my attempts have been thrown by this.
-In some cases, more than one cell will meet the criteria; this is expected.
-In some cases, I may not want to populate every criteria (to get more "rolled up" totals.
I realize this alone may be vague, though in reading examples, it can be hard to lay this out without a proper Excel file. I have created a simplified attachment that I believe covers the gamut of scenarios.
Any thoughts? Any help would be greatly appreciated. Thank you.