# Excel 2010 AVERAGE across multiple worksheets



## rappleby (Jan 1, 2008)

I need to average a set of figures across multiple worksheets.
In searching the net, I found a way to average cells across worksheets: =AVERAGE('Sheet1:Sheet10'!K5)

This will average the contents of the J5 cell across the range of worksheets between Sheet1 and Sheet10. I am using sales data on worksheets named for the year so my actual formula would be =AVERAGE('1998:2011'!K5). So, if manually entered the formula in the worksheet for 1998:2005, the average will be calculated for the years 1998 to 2005. 

Works just fine if I manually enter the worksheet names, but I am creating a template from which I can create a new worksheet each year. I also found a function that will provide the name of the worksheet in a cell:

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

so I am looking for a way to put this function in the formula so that when I copy the template and name the new sheet "2012" the =AVERAGE formula will be =AVERAGE('1998:2012'!K5). I have been able to successfully CONCATENATE the text in a separate cell using

=CONCATENATE("'","1998",":",MID(CELL("filename"),FIND("]",CELL("filename"))+1,255),"'")

yielding text of '1998:2012' but as soon as I try to substitute this in the AVERAGE function, I get standard AVERAGE error messages showing 
=AVERAGE(value1, value2,value3,....

Does anybody know how I can use the MID function in the AVERAGE function so that I can automatically generate the correct formula when I create and name a new sheet from the template????

To any and all responders - Thank You!


----------



## etaf (Dec 28, 2008)

indirect() may work 
so you can use it in a formula


----------

