INDIRECT function with worksheets in excel

0
votes

I am hoping to create a two dimensional matrix with a the indirect funciton in excel using something like this:

=INDIRECT(A12:A14 & "!C3:C11")

The idea is, if you have A12:A14 holding worksheet names, with C3:C11 in each of those worksheets holding some data, you can easily gather this into a matrix for processing within another worksheet using this simple function.

After this, you could use =MMULT() or other functions with arrays like B12:B14, for example to do a matrix multiplication:

=MMULT(B12:B14*INDIRECT(A12:A14 & "!C3:C11")

Is there a simple way to do this that I am missing? For instance rearraging it with parenthesis or placing a SUM somewhere?

2
excel

2 Answers

3
votes

Array formula**

=MMULT(TRANSPOSE(B12:B14),N(OFFSET(INDIRECT("'"&A12:A14&"'!C3"),TRANSPOSE(ROW($C$3:$C$11)-MIN(ROW($C$3:$C$11))),)))

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

1
votes

If you use the construction

=INDIRECT(A12:A14 & "!C3:C11")

that doesn't create a matrix but it creates an "array of ranges". AFAIK you can only use that usefully with certain functions, e.g. the "IFS" type functions like SUMIF, so you can get the total count of positive values in those 3 ranges with this formula

=SUMPRODUCT(SUMIF(INDIRECT(A12:A14 & "!C3:C11"),">0"))

Another function that can process that arrangement is SUBTOTAL, so this formula will sum all the values in those 2 ranges

=SUMPRODUCT(SUBTOTAL(9,INDIRECT(A12:A14 & "!C3:C11")))