Excel- many sheets one summary auto

[J-Will]

I am compiling responses, the responses are coming in on an Excel sheet (two sheets per response). Is there a way to have a master spreadsheet, paste all the responses in to this spreadheet, and have a summary sheet automatically make tallies?

A1 on all the tabs hold a value, x tabs. Summary sheet adds all the values of A1. But here's the catch... automatically change the formula when more tabs are imported/ pasted?

Cliff: Excel sheet will grow over time, can I have a summary tab automatically add all the values in cell A1 across all the tabs, and placed in A1 of summary tab. If a tab is added, it will automatically get added to the SUM of A1 and placed in the summary.

 

[Pwyl_The_Destroyer]

You could probably do this with some VBA. I don't know about its ability to respond to events like pasting in a sheet, but you can definitely write code that works with the sheet collection.

 

[J-Will]

You could probably do this with some VBA. I don't know about its ability to respond to events like pasting in a sheet, but you can definitely write code that works with the sheet collection.

That would be great. The template for all the sheets is identical, even the summary sheet. I just need the summary to auto update (even if its just hitting an update button to activate VBA cose) as completed sheets are added.

 

[CEpeep]

Can you use a Pivot Table? I think it has the ability to evaluate all tabs of a document, though you may have to manually refresh it when you add a new tab.