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?

 

[Derek Brown]

Yes, it is because it is within the quotes.
Try
=AVERAGE(IF(N(INDIRECT("'"&$W$4:$W$11&"'!"&E4))>0,N(INDIRECT("'"&$W$4:$W$11&"'!"&E4))))

 

[zakasnak]

Yes, it is because it is within the quotes.
Try
=AVERAGE(IF(N(INDIRECT("'"&$W$4:$W$11&"'!"&E4))>0,N(INDIRECT("'"&$W$4:$W$11&"'!"&E4))))

So it doesn't NEED the quotes, then?

 

[zakasnak]

Another question. Can I add error checking to this array formula?

 

[zakasnak]

Yes, it is because it is within the quotes.
Try
=AVERAGE(IF(N(INDIRECT("'"&$W$4:$W$11&"'!"&E4))>0,N(INDIRECT("'"&$W$4:$W$11&"'!"&E4))))

I'm getting a #REF error.

 

[zakasnak]

Bump

 

[zakasnak]

I'm on Row 86 of find & replace.... Please help?

 

[zakasnak]

..... Row 189 of find & replace.... Please help?

Can it not be done?

 

[zakasnak]

Last bump....... still finding & replacing ... in EACH ROW.

VBA doesn't scare me if someone can help me get started.

 

[Aladin Akyurek]

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?

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)))))