Referancing differant sheets

[Guest]

HEADING THAT IS ALWAYS THE SAME
A B C D E
1 BRAND VAIETY YEILD UNITS
2 Co .X A123 213 Bu/A (DATA ALWAYS USES THE SAME
CELLS)
3 Co. X B456 201 Bu/A
4 Co. Y X098 210 Bu/A
5 Co. A H678 225 Bu/A
FOOTER THAT IS ALWAYS THE SAME

I have 50+ sheets that have this information (differant brands Varieties and
differant yeilds)
I am trying to extract data from these sheets and compare them on a summery
sheet. My summery sheet looks something like this
A B C
D E
1VARIETY
X098 H678
2
AVERAGE YEILD
201.98 204.45
(AVERAGE OF ALL SHEETS)


3
#OF TRIALS 3 6
(COUNTS TIMES
VARIETY IS IN REST
OF WORKBOOK)

4 A123 213.89 12


5 B456 211.90 2
IN MY SUMMERY I AM TRYING TO FIND HOW MANY TIMES TWO VARIETIS ARE ON THE
SAME SHEET AND THEN FIND THE DIFFERANCE IN YEILD TO APPLY TO CELL D4 D5 E4 E5
AND ETC. so if variety a123 and variety X098 are on 15 sheets togather I want
excel to average their yeilds find the differance and put that in cell D4
Simple.... I am getting better and better at excel but not there yet.
thanks for any help or pointing me in the right direction

 

[Bernard Liengme]

Cant; following example as you are shouting - get rid of the caps!
Here are some formulas to experiment with
1) =Sheet2!A1
2)=SUM(Sheet1:Sheet5!A23)
best wishes

 

[Domenic]

For the number of instances where two varieties, such as 'A123' and
'X098', are contained in the same sheet, try...

=SUMPRODUCT(--(COUNTIF(INDIRECT(A1:A50&"!B:B"),"A123")>0),--(COUNTIF(INDI
RECT(A1:A50&"!B:B"),"X098")>0))

....where A1:A50 contain your sheet names. How are you determining the
average yield?

Hope this helps!