I am providing a sample spreadsheet to illustrate what I am currently doing and how I would like to improve it:
TAB CALLED "26_MAY_2016"
This spreadsheet is normally linked to specific columns in another excel workbook to pull in the essential data for dates that tasks were completed and number of minutes the task took in our book production process for each of the 3 types of books: CAP, GPH and RCAP. For the purposes of providing a sample spreadsheet, I have broken those links and am providing static data.
Names of books
This is a helper for Column A that trims the book names to the first 3 characters because I didn't know how to match a partial text string in my SUMPRODUCT formula, so that I could total by the three types of books: CAP, GPH and RCAP.
I want to delete this helper Column by improving my formula so that it matches the first 3 characters of the data in Column A.
Columns C through P
Date columns and minutes columns for the 7 book production tasks (there are a total of 30+ book production tasks, but for the purposes of this example I am just providing 7).
TAB CALLED "MAY_Results"
Rows 1 through 13
This is an 'intermediate' table of calculations to enable me, due to my limited excel expertise, to achieve the FINAL RESULTS (as shown in rows 16 through 20 on the same tab)
Rows 16 through 20
The FINAL RESULTS table is the desired output.
I would ideally like to bypass the above 'intermediate' table (Rows 1 through 13) by obtaining a formula that achieves the results shown in the FINAL RESULTS table.
The FINAL RESULTS table is linked to an external spreadsheet for my manager to review/access.
I'm looking for a 'no maintenance' solution in providing these metrics to my manager — once the formulas and links are in place, the numbers will update automatically without intervention by me.
If the formulas can reference the Volume column (column A) in the 26_MAY_2016 tab, thereby eliminating the necessity of the helper column called 'Vol_Trim' (Column B) in the same tab, that would be ideal.