Can you create a new spreadsheet from an old one?

[bmoos1]

I am not an Excel person; however, I have a quarterly report that is in Excel and consists of 2 pages. I inherited the report when we started telecommuting and we lost our administrative support.

The report's design was sent to me and it worked well for 1 fiscal year. Because it keeps track of the amount of funds that are billed on a particular contract, instead of sending me a new spreadsheet for the next year, they (my client) decided to just keep going on this particular spreadsheet because they were extending the contract. I copied and pasted the spreadsheet, but I had to go through and take out all the numbers, change all the dates, etc. Should be easy right? Page two of the spreadsheet takes the numbers from page two and sums up everything. I messed this up this last Fiscal year and now they want to continue it again.

Can this process be made simpler? The spread sheet is now 4 pages (2 for year 2016 and 2 for year 2017). I'm willing to share the sheets with anyone that wants to look at it and help me out.

 

[JLGWhiz]

Making a template would be the route to go. To make a template, save the file with the two sheets (I assume that is what you meant by pages) under a different name. Use the new file to set up the template. Do Not delete any data when a formula appears in the display box at the top of the screen and do not delete your headings or other static data which will not change throughout the year. Delete only the data that matches what appears in the display box. This should leave your worksheets with only headings and formulas which can be used for a template. When you need a file, simply do a SaveAs for the template to the FY report name you want to use.

 

[bmoos1]

I tried that last year, but the links disappeared and I had to try to link them up again. I did a bad job of this. Since the spreadsheet is not 4 pages, is there a way to make a template of the original 2 pages and keep them linked, or do I have to go back to the original spreadsheet that was sent to me in 2016?

 

[JLGWhiz]

The formulas are what links the data. That is why formulas should NOT be deleted. Only delete the data when the fromula bar display shows the data value the same as is displayed in the cell. Those are the cells which either are filled by external source or manually. The formulas then use that data to populate the other cells. I don't know that I could write code to do that for you because of the headings and other static entries that might be in the worksheets. Surely there is someone you work with who can assist you to prevent the loss of your formulas and links. It would be extremely difficult to create a template for you from remote locations without having your file and I don't accept files by personal email for security reasons. But there are people smarter than me who monitor this site and they might have a better solution.