Summary/Overview Page

[joeyharris]

This was originally posted on another thread. I was advised to go ahead and post here.

I have between 10-25 jobs that I am job costing for each month. Is there a simpler way to achieve a summary page if I need to show Revenue - Expenses = Net Income for each tab than manually referencing each cell.

Also, is there a better way to duplicate the file for the next month than opening the spreadsheet and "Save As" the next month. I have to go through and delete all the old info if I do it this way.

Any help would be excellent.

[#2]

@joeyharris Yes to both requests. The main trick behind having the summary tab more automated is to make sure the tab format for all Vendor Invoices are the same. You can then apply an INDIRECT and CONCATENATE formula combination:

=INDIRECT(CONCATENATE("'",A4,"'!D23")))

I've attached an updated version of your file that you can compare. The Naming structure under your projects should have a perfect match with the tab name to be referenced correctly.

Regarding duplicating the file, wouldn't saving a blank template working template that retains all formulas suffice?

[#3]

@joeyharris Consider tracking all your transactions (income and expenses) in one single table and create one or more pivot tables to summarise the date. No need for multiple sheets or formulae linking them to a separates summary sheet. Simply add transactions to the table (will expand automatically) and refresh the pivot table(s). An example is attached.