@Debi_Evans
If it's not asking you to share state secrets (or other confidential information), might I be so bold as to ask for a more complete description of the project here?
My reason for asking is that whether or not the precise thing you're asking is possible--I am pretty confident it is--it sounds on the surface like you may be taking an approach that takes something relatively easy to do and makes it harder. Now, there may be entirely legitimate reasons to have to have an indeterminate number of copies of essentially the same sheet, all to be summarized and reported on in a master sheet; I will acknowledge that.
My reason for asking, however, is that this is also a common mistake made by relatively new Excel users. We see it frequently here on this website.
- Somebody will have created a sheet for weekly input on, say, hours worked, and then need a way to combine data from all those weekly sheets into monthly or quarterly or annual reports.
- Or there's a workbook created to track output from each of multiple production lines, or sales from multiple stores, each entity to be tracked in its own sheet.
- And so forth.
The reality in almost every case is that Excel excels in being able to take into just one sheet with the information on hours worked by all the workers for every day of the year and then break it apart into summary reports by week, month, quarter, year....
Similarly for production by multiple lines--put the data all on one sheet and then let Excel do the "heavy lifting" of summarizing in whatever way is needed.
So, that's why I ask. If you'd be willing to post a copy of the workbook you already have, along with a more complete description of the context itself, the purpose to be served, it's entirely possible that we could provide you a more effective way to accomplish the goals you have in mind.
