There's a lot going on in that formula, and this is a fairly advanced technique. Let's analyze it from the inside out.
The INDIRECT function says, "Give me a string, and I'll give you back the range that it refers to." To take a simple but useless example
=INDIRECT("A1")
would be exactly the same as
=A1
INDIRECT is useful when you want to refer to a range that depends on some other value to identify it. One common example is using a cell to identify a sheet name, which is kinda sorta what you are trying to do. In your case the name October12 represents the range
'Summary Sheet - October 2012'!$R$3:$R$7
If you are using the name October12 in a formula, it returns list of values from that range. It is used as part of a string to refer to a range, so
INDIRECT("'"&October12&"'!C:C")
ends up resolving as
'1st - 5th Oct 2012'!C:C
'8th - 12th Oct 2012'!C:C
'15th - 19th Oct 2012'!C:C
'22nd - 26th Oct 2012'!C:C
'29th - 31st Oct 2012'!C:C
as Excel substitutes each value in the list.
How can one formula have five different values? Well, normally it doesn't, and Excel would only use the first value in the list. But your formula is wrapped up in a SUMPRODUCT function. SUMPRODUCT has some special properties. Excel is generally a static tool, but SUMPRODUCT allows it to do a kind of iteration. SUMPRODUCT will look at each argument, and if it's an array (that is, a range of multiple cells), it will calculate the answer for each value of the array and then add them together.* In this case, the SUMIF function sums up the quantity for a given order number on a specific worksheet. Putting it SUMPRODUCT means that we're going to do it for each value of October12, which means we're going to repeat the sum for every worksheet.
This formula is quite clever. I've never seen SUMPRODUCT used with a range used inside an INDIRECT call and didn't realize it would work. I hope this explanation is helpful. It would be much easier to explain to you in person.
BTW I have to add that I would have designed this to have all the data in one worksheet to begin with, which would make everything else you need to do immensely easier such that your summary could be done automatically with a pivot table.
________________________________________
*In this formula, SUMPRODUCT only has one argument. It can have multiple arguments. When it does, it multiplies them all together, which is why it's called SUMPRODUCT--it gives the sum of all products generated as it iterates through the ranges. The classic use is to multiply each value in one column times its corresponding value in another column and return the sum of all those multiplications. For example, if you have an invoice with a quantity column and a unit cost column, you could use SUMPRODUCT to calculate the total invoice amount in one formula instead of adding another column.
Bookmarks