Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remote 3D reference array
I need, in a summary book, to calculate a weighted average from a
remote book, each sheet of which contains monthly data. Each monthly sheet contains a monthly average and number of items used to calculate that average. I want to multiply the two numbers on each sheet, sum the products across all sheets, and (easy part) divide by the total number of items used to calculate all the averages. That is to say, take a weighted average. The following does not work, and I understand why. But, if it did, it would do what I want to do. =SUMPRODUCT('[XXXReport_AD through Nov.xls]Jul:Nov'!E$7*'[XXXReport_AD through Nov.xls]Jul:Nov'!D$7) If this weren't a remote reference, I would just list the names of the sheets and use INDIRECT in the array formula. It has to be remote, not only practically, but also for my own edification. Anyone have any formula-based solutions, or failing that, some slick VB code? Thanks for any help. Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use of Offset function in array formula | Excel Worksheet Functions | |||
Use one array as a reference for another | Excel Discussion (Misc queries) | |||
Index ( ) | New Users to Excel | |||
VLOOKUP keeping array lookup reference | Excel Discussion (Misc queries) | |||
How can I use a cell reference in Sumproduct array formula? | Excel Discussion (Misc queries) |