average array across multiple sheets

[zakasnak]

I have this array formula that works perfectly.

=AVERAGE(IF(N(INDIRECT("'"&$W$4:$W$11&"'!E4"))>0,N(INDIRECT("'"&$W$4:$W$11&"'!E4"))))

My problem is that when I copy this formula thru the thousand or so rows (sorry, 969 rows to be exact), the *row* indicator '!E4 does not update. Is this because it's in quotes?

Is there a way either thru the formula or VBA that I can update this part of the formula for the correct row on every row? I have 969 rows & 8 columns (E, F, G, I, J, L, N & O) to update. Help?

 

[zakasnak]

Control+shift+enter, not just enter, and copy across...

=AVERAGE(IF(N(INDIRECT("'"&$W$4:$W$11&"'!"&CELL("address",E4)))>0,
     N(INDIRECT("'"&$W$4:$W$11&"'!"&CELL("address",E4)))))

PERFECT! Thank you so much!