1

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?

rohannair
  • 31
  • 7
  • How do those dates compare to today's date? Try indirect() with today() to build each reference. today() - 1 or today() - 2 etc – Solar Mike May 17 '23 at 06:43
  • 2
    Any reason why you don't have one big data table to which you add each days data - and then build the necessary reports on that base? Also look for 3D-reference: e.g. https://exceljet.net/formulas/sum-across-multiple-worksheets – Ike May 17 '23 at 06:45
  • If you do not use space in sheet name and you have `M365` then could use `TOCOL()` or `VSTACK()` to get all those data at once like `=TOCOL(Shift_1_May_2:Shift_1_May_3!A1:A5)`. – Harun24hr May 17 '23 at 06:47
  • @SolarMike Basically i have to key in data in during the day. So technically its live data. "today()" – rohannair May 17 '23 at 06:51
  • today() is a volatile function ie every time you enter data ie change a cell then today() will recalculate so about as live as you are... – Solar Mike May 17 '23 at 06:57
  • It can't take you 30 min to change one formula. What takes you so long? Are you manually changing dozens of formulas? How is the report structured? How are the source data structured? A possible solution might be creating a table with the ranges' addresses. A more elegant one might be using VBA. – Evil Blue Monkey May 17 '23 at 07:16

3 Answers3

4

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.

Evil Blue Monkey
  • 2,276
  • 1
  • 7
  • 11
2

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:

enter image description here

enter image description here

enter image description here

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

enter image description here

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:

enter image description here

enter image description here

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

enter image description here

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.

user11222393
  • 3,245
  • 3
  • 13
  • 23
0

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

Philippe G
  • 1
  • 2