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:

Invoice No	Invoice Date	Amount	Control No	Status
TEMPLATE	=today()
1052	12/21/2013	$5,583	283	Paid
1053	01/15/2014	$6,784	295	Paid
1054	02/01/2014	$5,583	300	Unpaid
1055	02/15/2014	$4,000	311	Unpaid
1056	03/01/2014	$1,250	318	Unpaid
...	...	...	...	...

<tbody>
</tbody>

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