3

The situation

  • In the sheet "Planning" I have an area that contains pairs of sessions (strings) and hours (numbers) in adjacent cells (e.g. D11 and E11, I12 and J12 etc.) One session can occur multiple times.
    • D11:E11 is | Foo | 8 |
    • I12:J12 is | Foo | 4 |
  • In another sheet, I want to find a session in the Planning sheet and return an array with all the hours booked on that session (to calculate a total)
  • I use an array formula with a conditional and intend to use the SMALL function to retrieve the results from the array

The problem

The following formula returns all the correct references to hours booked on "Foo", so far so good.

=IF(Planning!$D$11:$CV$18="Foo";ADDRESS(ROW(Planning!$D$11:$CV$18);COLUMN(Planning!$D$11:$CV$18)+1;;;"Planning"))

{"Planning!$E$11"\FALSE\FALSE\FALSE\FALSE\"Planning!$J$12"}

However, if I use the INDIRECT function to retrieve the values of those references, they always return the value of the first reference in the array ("Planning!$E$11")

=IF(Planning!$D$11:$CV$18="Foo";INDIRECT(ADDRESS(ROW(Planning!$D$11:$CV$18);COLUMN(Planning!$D$11:$CV$18)+1;;;"Planning")))

{8\FALSE\FALSE\FALSE\FALSE\8}

How do I retrieve the correct values? Or should I tackle the problem in a whole different way?

Screenshots

Community
  • 1
  • 1
krowl
  • 31
  • 1
  • 5
  • I'm not sure indirect is what you want to use. Difficult to say though, do you have an example (screenshot or something similar) of the data and expected outcome? – a-burge Aug 08 '18 at 10:21
  • The way you are using indirect currently, is finding the value of the first cell in your reference range (e11 being one column to the left of d11). – a-burge Aug 08 '18 at 10:26
  • Thanks. I added the screenshots. By the way, wrapping the INDIRECT around the array is not a solution either: all FALSE Booleans are converted to #REF!, which renders the array unusable for SMALL. – krowl Aug 08 '18 at 11:26
  • Due to the format of the data, it would not be possible to generate the overview you are aiming to, however, you can use formulas to generate a consolidation table then use a PivotTable to generate the desired summary. – EEM Aug 10 '18 at 20:36

2 Answers2

0

Since I was mainly interested in the total of planned hours, I eventually used the following formula:

=SUM(SUM(INDIRECT(IF(Planning!$D$11:$CV$18="Foo";(ADDRESS(ROW(Planning!$D$11:$CV$18);COLUMN(Planning!$D$11:$CV$18)+1;;;"Planning"));"$U$19"))))

  1. IF: Create the array with references to the Planning sheet if the string is found. If it's not found, add the reference $U$19.
  2. Using INDIRECT, replace all references with the values in the Planning sheet. $U$19 contains the value 0.
  3. Then use SUM twice to sum up all the values. I don't know why, but see
krowl
  • 31
  • 1
  • 5
0

Indirect doest work in most array formulas. If you give it a string that refers to an array, like "A1:A10" it it returns those cells as expected but thats about it. You can use that array as the input to another function but you cant send an array output from another function to INDIRECT(). (Or at least i have not figured out a way)

Try using the INDEX function with the ROW function.

INDIRECT("A1:A10") is similar to

INDEX(A:A,ROW(A1:A10))

However the former is less flexible.

Comsider:

INDEX(A:A,FILTER(ROW(A1:A10),NOT(ISBLANK(A1:A10))*ISNUMBER(A1:A10)))

This returns an array containing the numerical values in the range but does not treat an empty cell as zero. Watch your order of operations and parenthesis.

The product NOT(ISBLANK(A1:A10)*ISNUMBER(A1:A10) is the inner product of two vectors of boolean values.

ROW(A1:A10) creates a vector of row values of the of the elements in that range. Then filter throws out any where the corespinsing element of the boolean vector is 0. Index then returns an array of values of the cells in its range coresponding to those rows. The range given to INDEX could be any row in fact. Not just the one your selecting on. Using the entire column (for example A:A) allows excel to automatically update the references if you move the source data, for instance if you insert a header row. If you use a specific range you will need to add an offset to the row value and it will not automatically update refernces. (Without a far more complex formula)

Michael Allen
  • 1