Search code examples
excelwps

is there an easier way to write this formula

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?

Solution

  • 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.