Indexing Sheets in Referenced Workbook within formula
I have the following formula:
=AVERAGE('[#1 Turb-Jun09.xls]7'!$E$164,'[#1 Turb-Jun09.xls]7'!$D$164,'[#1 Turb-Jun09.xls]7'!$C$164) It references a monthly workbook (#1 Turb-Jun09.xls) with a sheet for every day in the same workbook. In this case, I am averaging three cells on the sheet labeled 7. The data points and cells are identical (although not in value) in every sheet. All I wish to do is copy this formula to replace the three 7's with an 8, then 9, then 10 and so on without manually doing that. I have tried filling out 7 of them to see if autocomplete could recognize the pattern when they are highlighted, but it doesn't. Thank you for your help. |
Indexing Sheets in Referenced Workbook within formula
Try
=AVERAGE( INDIRECT("'[#1 Turb-Jun09.xls]"&ROW(A7)&"'!$E$164"), INDIRECT("'[#1 Turb-Jun09.xls]"&ROW(A7)&"!$D$164"), INDIRECT("'[#1 Turb-Jun09.xls]"&ROW(A7)&"'!$C$164")) HTH Bob "tiazmos" wrote in message ... I have the following formula: =AVERAGE('[#1 Turb-Jun09.xls]7'!$E$164,'[#1 Turb-Jun09.xls]7'!$D$164,'[#1 Turb-Jun09.xls]7'!$C$164) It references a monthly workbook (#1 Turb-Jun09.xls) with a sheet for every day in the same workbook. In this case, I am averaging three cells on the sheet labeled 7. The data points and cells are identical (although not in value) in every sheet. All I wish to do is copy this formula to replace the three 7's with an 8, then 9, then 10 and so on without manually doing that. I have tried filling out 7 of them to see if autocomplete could recognize the pattern when they are highlighted, but it doesn't. Thank you for your help. |
All times are GMT +1. The time now is 12:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com