No, A2:A14 should contain all the sheet names like

Sheet1
Sheet2
Sheet3
Sheet4
...........
Sheet13


so each cell in A2:A14 holds a sheet name

then you just use Domenic's formula

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"curtll" wrote in message
...

So in your formula where you have ("'"&$A$2:$A$14&"'!B2"), I should
enter it like this....("'"&Sheet1:Sheet13&"'B2")....???

Domenic Wrote:
Let A2:A14 contain the sheet names, then try the following formula,
which needs to be confirmed with CONTROL+SHIFT+ENTER...

=AVERAGE(IF(N(INDIRECT("'"&$A$2:$A$14&"'!B2"))0,N( INDIRECT("'"&$A$2:$A$1
4&"'!B2"))))

Hope this helps!

In article ,
curtll wrote:

Hello,
I have 14 worksheets in my current workbook, 13 of which are
different employees. Each day I am entering data that is then
automatically averaged into a mtd column. Sometimes there will be
0's
in those mtd columns and I dont want those counted when i take the
number from the single cell on all of the worksheets to produce a
correct average on sheet 14 Please help i've been working on this for
3
days. Here is a list of different formulas Ive tried.


=SUM(sheet1:sheet13!B2)/COUNTIF(sheet1!B2,"0",sheet2,"0",sheet3,"0",sheet4 ,

"0",sheet5,"0",sheet6,"0",sheet7,"0",sheet8,"0",sh eet9,"0",sheet10,"0",
sheet11,"0",sheet12,"0",sheet13,"0")

=AVERAGE(IF(sheet1:sheet13!B20,sheet1:sheet13!B2," ")

And other variations of those....either I get a REF or VALUE error
when
doing this. Ive searched and searched the last 3 days and im about to
give up. Please help


--
curtll