#REF error in formula in first copied sheet.

[davekoshinz]

Hello and thank you.

I have a workbook with the following formula =SUM('1:Goals'!I3)/(SHEETS()-1)

The formula is in a sheet named "1" and there is another sheet after it named "Goals"

When I copy the sheet "1" it creates a new sheet between "1" and "Goals" called "1(2)". This formula breaks on the first copy with a #REF, such as =SUM(#REF!I3)/(SHEETS()-1)

If I then correct the formula back to the original in "1(2)" and then copy it to "1(3)" the formula works in "1(3)" it seems to break only on the first copy.

The "Goals" sheet has nothing in "I3" and doesn't need to be included in the formula except (I believe) to create an end point for summing the sheets.

What I want to do is to start each month with "1" and "Goals" then copy the previous sheet (starting with "1") each day of the month and have this formula create a running average of the sheets. This is outside of my pay grade, please give me some suggestions. Thank you...Dave

[#2 · Accepted solution]

@davekoshinz 

Create an empty sheet before '1', and name it (for example) Dummy.

Use =SUM('Dummy:Goals'!I3)/(SHEETS()-2)

Copying the '1' sheet should not cause #REF! now.

[#3]

Thank you, this works.