Hi Folks
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),"'")
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!
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),"'")
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!
Last edited: