|
Formula to extract data from other worksheet
My problem is this. The following formula:
=(('Budget MRS 2007'!$H6*'Budget MRS 2007'!$H7)+('Budget MRS 2007'!$I6*'Budget MRS 2007'!$I7)+('Budget MRS 2007'!$J6*'Budget MRS 2007'!$J7)+('Budget MRS 2007'!$K6*'Budget MRS 2007'!$K7)+('Budget MRS 2007'!$L6*'Budget MRS 2007'!$L7)+('Budget MRS 2007'!$M6*'Budget MRS 2007'!$M7))/I6 Is extracting data from one sheet "Budget MRS 2007" and returning a result. But I need to use this formula to calculate a lot of data. The above formula e.g. use cell H6-M6 and H7-M7 but I need results for H11-M11 and H12-M12 and so forth it increases with 5 cells everytime. Is there a smart way that I can make a formula or something, so that I don't have to manually change the cellnumbers that excel needs to extract data from, but make excel realize that it need to take 5 cell steps every time. Hope this makes any sence or else dont hesitate to write back for further explanation. Thanks |
Formula to extract data from other worksheet
Another thought ..
Try this in a cell adjacent to your posted expression: =SUMPRODUCT(OFFSET('Budget MRS 2007'!H$6,ROWS($1:1)*5-5,,,6)*OFFSET('Budget MRS 2007'!H$7,ROWS($1:1)*5-5,,,6))/I6 It should return the same result as your expression. Copy the formula down one cell, it'll return a corresponding result, but for H11-M11 and H12-M12 instead (ie an increase of 5 rows down as desired). Copy down as far as required. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "TD" wrote: My problem is this. The following formula: =(('Budget MRS 2007'!$H6*'Budget MRS 2007'!$H7)+('Budget MRS 2007'!$I6*'Budget MRS 2007'!$I7)+('Budget MRS 2007'!$J6*'Budget MRS 2007'!$J7)+('Budget MRS 2007'!$K6*'Budget MRS 2007'!$K7)+('Budget MRS 2007'!$L6*'Budget MRS 2007'!$L7)+('Budget MRS 2007'!$M6*'Budget MRS 2007'!$M7))/I6 Is extracting data from one sheet "Budget MRS 2007" and returning a result. But I need to use this formula to calculate a lot of data. The above formula e.g. use cell H6-M6 and H7-M7 but I need results for H11-M11 and H12-M12 and so forth it increases with 5 cells everytime. Is there a smart way that I can make a formula or something, so that I don't have to manually change the cellnumbers that excel needs to extract data from, but make excel realize that it need to take 5 cell steps every time. Hope this makes any sence or else dont hesitate to write back for further explanation. Thanks |
Formula to extract data from other worksheet
Is there a way for me to extract data from a table when it's updated to
another spreadsheet? I have a table with populated data that needs to be updated daily, and instead of looking at the whole table. looking for the latest updates, I would like the latest updates/input to be extracted to another spreadsheet, which lay out only latest update. My table doesn't involve any calculation, just comments & remarks +date venn "Max" wrote: Another thought .. Try this in a cell adjacent to your posted expression: =SUMPRODUCT(OFFSET('Budget MRS 2007'!H$6,ROWS($1:1)*5-5,,,6)*OFFSET('Budget MRS 2007'!H$7,ROWS($1:1)*5-5,,,6))/I6 It should return the same result as your expression. Copy the formula down one cell, it'll return a corresponding result, but for H11-M11 and H12-M12 instead (ie an increase of 5 rows down as desired). Copy down as far as required. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "TD" wrote: My problem is this. The following formula: =(('Budget MRS 2007'!$H6*'Budget MRS 2007'!$H7)+('Budget MRS 2007'!$I6*'Budget MRS 2007'!$I7)+('Budget MRS 2007'!$J6*'Budget MRS 2007'!$J7)+('Budget MRS 2007'!$K6*'Budget MRS 2007'!$K7)+('Budget MRS 2007'!$L6*'Budget MRS 2007'!$L7)+('Budget MRS 2007'!$M6*'Budget MRS 2007'!$M7))/I6 Is extracting data from one sheet "Budget MRS 2007" and returning a result. But I need to use this formula to calculate a lot of data. The above formula e.g. use cell H6-M6 and H7-M7 but I need results for H11-M11 and H12-M12 and so forth it increases with 5 cells everytime. Is there a smart way that I can make a formula or something, so that I don't have to manually change the cellnumbers that excel needs to extract data from, but make excel realize that it need to take 5 cell steps every time. Hope this makes any sence or else dont hesitate to write back for further explanation. Thanks |
| All times are GMT +1. The time now is 10:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com