Sorry if my title wasn't clear enough. I work with a subconsultant who does all my financial analysis on every project that I complete. And on every project we complete he has a template Excel workbook which he fills in that contains over 20 tabs. The workbook has a lot of horsepower in terms of financial analysis, but not so much in terms of end-user ease-of-use or visual appeal. The simple fact that is keeping my subconsultants workbook back in terms of end-user ease-of-use and visual appeal is that this is a template spreadsheet, and was designed with data entry and analysis in mind, not presentation. I am hoping I can either a) build a separate template "presentation" workbook that pulls from his workbook as a source or b) build some tabs into his workbook that provide some more user and visual friendly data.

So, the first issue is that there are still columns with headings like "InsertName7" that are unchanged and have no values associated with them. It's not just one or two of these columns in one or two tables either, it's 8 to 10 of these columns in 8 to 10 of these tabs. And its like this because "InsertName7" gets typed in once, but shows up 10 times. What I'm hoping for is some way to use my subconsultant's template workbook as a source workbook for a more filtered workbook that only includes real values and their corresponding rows/columns like "John Doe" and not "InsertName7". The concern I have is how to make this repeatable with little effort on my end? The names of his template workbook will change but the tabs and cell references will not. Is there a way I can have my own template filter document and just update the source document when I get it?

The next issue is the fact that we generally perform financial analysis over a period of 5 to 35 years, but his template spreadsheets allow for a period of 50 years before listing the total amount of revenue. So, when I receive the spreadsheet there are typically 15 to 45 blank cells in several tabs. Is there a way that I could use a formula or a filter to put only "real" or non-zero values into a new spreadsheet, and have the total value reflect in the proper spot?

Thanks in advance for any help provided, your time is greatly appreciated.