Average of a cell across multiple sheets that allows adding sheets

[davekoshinz]

Hello, I am trying to have a running average of a particular cell across a range of sheets that grows.

I'm using this formula =SUM('1:1 (9)'!I17)/(SHEETS()-2) 

I'm copying the last sheet in each day so will be creating 1 (10), 1 (11), etc.

I want to have this formula on the first sheet 1 and copy that sheet each day of the month 1 (2), 1 (3), etc. so the formula will show the running average of the cell in all the sheets that have been created.

Thank you for any ideas you can provide...Dave

[#2 · Accepted solution]

@davekoshinz 

I'd add an empty sheet after the last sheet with data. Name it Dummy. You can then use

SUM('1:Dummy'!I17)

When you have to copy the last sheet with data, insert the copy before the Dummy sheet.

That way, the sum will always be correct. without you having to adjust the formula.

[#3]

@Hans Vogelaar wow, thank you that is so simple now that I know the answer. Dave