Summarizing a lot of tabs

[Miro H]

Hello,

I need help. I will try to describe the problem I have as best as possible.

I have an Excel workbook which is tracking workhours (among a lot of ohter things) spent on different items - i will call them ITEM.

This is the structure of the workbook and parameters:

• Main worksheet
  • one row for each ITEM
  • aprox 30 columns for each ITEM which track different things (not important for my problem)
  • hyperlink for each ITEM within the same workbook which opens the worksheet of this ITEM
• 200 sub-worksheets which track work hours (this is the aprox. number of worksheets per file/year)
  • each row has - date, number of people, names, total hours per day, total hours per ITEM
  • there is no rule when the work hours for each ITEM can happen. It can be several consecutive day, it can be randomly, it can be nothing for one month then again 1 day etc.
  • each sub-worksheet has the same structure and not a huge amount of entries
• Several people work on the same workbook
• There are aprox. 10-20 new ITEMs/worksheets per month
• No rule on the position of the new worksheet in the document (in the end, in the beginning)
  • It can appear anywhere in the workbook depending where is the person who clicks to add the new worksheet positioned in the document at that moment
• Excel is prefferd solution as the people who work on the workbook are familiar with it

My goal:

• to summarize work hours for each month (listed for each date of the month and total)
• I need only total per each date in the month and total per month. I don't need it to be summarized per ITEM
• I need to do this month by month

Tried solutions and problems:

• consolidate
  • works well
  • Problem - each worksheet that I want to consolidate must be entered manually - didn't find a way to shif-select all tabs and consolidate them
    • since each new tab can appear anywhere in the document I have to select all tabs from scratch for every new month otherwise I could leave out some ITEM
    • is there any way to use consolidate with shift-select?
• power query + pivot table
  • works well
  • Problem - the same as above, each worksheet I have to enter manually in power query table. Even slower to select then consolidate.

Does anybody have an idea how could I make this easier and faster?

If not, does anyone has any proposal how to structure the document differently or maybe use some different tools?