Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referancing differant sheets
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referancing differant sheets
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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "jp nellie" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referancing differant sheets
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! In article , jp nellie wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilter Lists across Multiple Sheets, Maintain Correct Referenc | Excel Worksheet Functions | |||
In 3 active sheets in wkbk, determine& display the # of sheets that have data | Excel Discussion (Misc queries) | |||
Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
Multiple sheets selected | Excel Discussion (Misc queries) |