Please explain how this formula works, and the limitations of INDIRECT function

**jool ya** · 11-07-2012, 08:11 PM

I'm relatively new to excel so I hope this makes sense.

Basically my workbook consists of several sheets representing each work week within a given month, plus a summary sheet at the end. Data for each job is entered progressively into the worksheets. In the past, the summary sheet has been calculated by entering each job in column C 'Sales Order Number', then doing a sum in the relevant column of all the related cells from previous worksheets. I'm trying to make the summary sheet completely automated so it calculates itself as data is entered in the other worksheets.

I found the basis for this formula on the internet and tweaked it to use in columns D and F-L of the summary sheet:

=SUMPRODUCT(SUMIF(INDIRECT("'"&October12&"'!C:C"),C3,INDIRECT("'"&October12&"'!D:D")))

As you can see in Column R of the summary sheet, October12 is a named range of all the individual worksheets for that month.

I don't understand exactly how the INDEX function works so I'm having difficulty using it in other formulas. If someone could explain the syntax, as well as any limitations of what other functions it can be combined with that would be so helpful!

Thanks

**6StringJazzer** · 11-07-2012, 11:00 PM

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.

**jool ya** · 11-08-2012, 12:07 AM

Thanks for your great reply - I've been re-reading it since you posted trying to wrap my head around it!

Can you (or anyone) offer advice on how to nest that INDIRECT reference with other functions? e.g a VLOOKUP that will search column x of all sheets (October12) for a value, and return corresponding data from column y? (I know that would only return the first value in the list, that's not a problem)

Basically I'm trying to understand what the string of text is, that will correctly reference that range of worksheets, so that I can write formulas for column B & E of the summary sheet (and possibly down the line column C)

**6StringJazzer** · 11-08-2012, 09:00 AM

Some (most?) functions are just not capable of dealing with ranges that cross multiple worksheets. VLOOKUP is one of those. INDIRECT by itself is not a way to write a formula that considers multiple sheets; the magic that allows you to do that in your formula above is SUMPRODUCT. INDIRECT is merely a way to reference a range by building the address on the fly, if the address to be used depends on other calculations you are doing.

In contrast, SUM does allow references across sheets. Using your file as an example, if I want to have a simple sum of all quantities of all products across all sheets, I could do this

Please Login or Register  to view this content.

will give you the sum of column D across all the sheets.

These concepts are not particularly well documented in Excel Help pages but there is a lot of information published on the web.