Summary Worksheet Witchcraft

[mjennaro]

Hello, MrExcel community. I currently have a workbook of my consulting company's invoices to customers. Each tab has a unique invoice number and client and amount but all tabs utilize the same template so information on invoice number is always in cell F4 across all worksheets.

That said, I would like to create a summary tab that aggregates specific fields from each worksheet. My vision for the summary tab would be as follows:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Invoice No[/TD]
[TD]Invoice Date[/TD]
[TD]Amount[/TD]
[TD]Control No[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]TEMPLATE[/TD]
[TD]=today()[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1052[/TD]
[TD]12/21/2013[/TD]
[TD]$5,583[/TD]
[TD]283[/TD]
[TD]Paid[/TD]
[/TR]
[TR]
[TD]1053[/TD]
[TD]01/15/2014[/TD]
[TD]$6,784[/TD]
[TD]295[/TD]
[TD]Paid[/TD]
[/TR]
[TR]
[TD]1054[/TD]
[TD]02/01/2014[/TD]
[TD]$5,583[/TD]
[TD]300[/TD]
[TD]Unpaid[/TD]
[/TR]
[TR]
[TD]1055[/TD]
[TD]02/15/2014[/TD]
[TD]$4,000[/TD]
[TD]311[/TD]
[TD]Unpaid[/TD]
[/TR]
[TR]
[TD]1056[/TD]
[TD]03/01/2014[/TD]
[TD]$1,250[/TD]
[TD]318[/TD]
[TD]Unpaid[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

With

Invoice No. being the name of the new tab as defined by me in the worksheet name field at the bottom of the tab;
Date being the date that exists within a cell in each tab (generated by using the "=today()" function
Amount being the sum of charges that exist within a cell on each tab (as generated by the "=sum()" function
Control Number being the control number I manually input to a cell in each tab
Status being a status I manually input to a cell in each tab

Again, all of these 5 fields have the same locations from one tab to another. I am just duplicating the TEMPLATE tab and then renaming the duplicate with the previous invoice number plus one.

The trick however, is that I want the "..." row above to have information updated automatically whenever I duplicate the template. In this case, the bottom cell would be TEMPLATE (2) until I rename the tab. TEMPLATE row will always look like that because the =today() formula will always display today's date but all other fields will remain empty in the summary because they are empty in the TEMPLATE tab.

In terms of process, when I create the tab for invoice 1057, the last Invoice No. cell in the bottom of the table above should automatically display TEMPLATE (2) until I change the name of the tab to 1057. I had a colleague at a former job that had a spreadsheet summary tab that would auto-populate the information from a new tab whenever it was created. If only I had kept in touch with her...

Hope this makes sense - sometimes making a video for these would be easier.

I am using Excel 2013 and hoping to avoid VBA and Macros but can make them happen in a pinch.

Thank you.

Michael