is there an easier way to write this formula

Question

I have to report daily data, and everyday I have to create a new sheet with today's date.

I have a summary sheet in the end which shows the sum for all the days.

I have this formula in my summary sheet =SUM(('Shift 1 (May 2)'!AY142:AY145),('Shift 1 (May 3)'!AY142:AY145),('Shift 1 (May 4)'!AY142:AY145),('Shift 1 (May 5)'!AY142:AY145),('Shift 1 (May 6)'!AY142:AY145)) Now, I have to edit this every day because a new sheet is created and it takes me 30-45 mins trying to edit the summary shit per day.

Is there a faster way to do this?

Answer 1

One of the ways would be to rewrite formulas using sheet range (note that not all formulas work with sheet range) instead of using separate sheet names. For example use 'Shift 1(May 2):Shift 1 (May 6)'!AY142:AY145 instead of your formula. For example you have Sheet1!A1:B1, Sheet2!A1:B1 and Sheet3!A1:B1 you want to SUM in Summary!A1:

Use formula with sheet range =SUM(Sheet1:Sheet3!A1:B1):

Keep in mind, that if you add new Sheet and don't want to rewrite formulas, you need to add that Sheet between Sheet1 and Sheet3. In case you need to keep those sheets to be sorted, you can use empty helper Sheet and keep it at the end of sheet list:

So next time you have to insert new sheet, put it before Sheet7 in this case:

However I strongly recommend to reconsider how report sheets are made so you can have all data in one sheet and create summary report using Pivot table or writing formulas referring to that one dataset sheet.

Answer 2

A possbile solution might be creating a table with all the needed reference produced dynamically. Paste this in a new sheet:

Word 1	ID 1	Date	Sheet name	Column 1	Row 1	Column 2	Row 2	Complete address	Sums	Total of sums
Shift	1	2023/05/01	=A2&" "&B2&" ("&PROPER(TEXT(C2,"mmm"))&" "&TEXT(C2,"dd")*1&")"	AY	142	AY	145	="'"&D2&"'!"&E2&F2&":"&G2&H2	=SUM(INDIRECT(I2))	=SUM(J:J)
Shift	1	2023/05/02	=A3&" "&B3&" ("&PROPER(TEXT(C3,"mmm"))&" "&TEXT(C3,"dd")*1&")"	AY	142	AY	145	="'"&D3&"'!"&E3&F3&":"&G3&H3	=SUM(INDIRECT(I3))

Of course it's still kind of intricate and i suspect that the report itself (or even the source data) might be improved in other ways.

Answer 3

I suggest using PowerQuery to first consolidate the data, and then extract the summary information from the consolidated data set. PowerQuery allows you to dynamically consolidate data through sheets using a certain name pattern (say 'Data-230517'). You then just need to refresh the query and it will adjust automatically to include new sheets with the the same name pattern.

There's a learning curve if you're not familiar with PowerQuery, but you can use this excellent tutorial from Leila Gharani:

Consolidate & Clean Multiple Excel Sheets in One Pivot Table