Formula help

[kirstypie]

I have a formula that looks like this:
=AVERAGE('1:30'!C42)

The formula is taking the average of a certain cell (C42) from each of the sheets numbered 1-30. Each sheet represents a day of the month.

However in one workbook that I am working on, it will change the formula to this:
=AVERAGE('1:[30]30'!C42)

If I delete the extra [30], it just puts it right back into the formula.

The formula that Excel puts in there gives a result of #REF!, so it's not working. Anyone know how I can make it take the formula that I type in there?

the weird thing is this formula works just fine in other workbooks that I have (for other months), so I don't know why it's putting up a fight in this one workbook.

 

[crook_101]

Hi,

The following formula will resolve your issue:

=AVERAGE(Sheet1:Sheet30!C43)

The formula that you suggested will not work on my 2007 - what version are you running??

Ian

 

[Akashwani]

Hi,

I don't mean to be rude but are you typing the formula in or copy/paste?

When I type the formula in it works ok (2007) but when I copy/paste Excel adds [30]
I also get #REF! when a sheet doesn't exist.

Just a thought

Ak