7

Suppose, that I have a vector of cells A1:A5, each with string that is an address of the other cell.

What I want to do, is to have a function, that returns an array of values from these referenced cells. See the example - I want the formula to return 15 (it returns 1).

enter image description here

In production setting, this vector is much longer (ca. 2000 rows).

What would solve the problem, is array-capable INDIRECT().

(I realize, that I might be pushing Excel beyond its the limits)...

My further thoughts on this topic:

Because the INDIRECT() is capable of producing arrays even from a single-cell input, such theoretical "INDIRECTARR()" formula would either require nested (i.e. multidimensional) array computation support in Excel, or will be intrinsically limited to arguments that denote one-dimensional ranges.

Adam Ryczkowski
  • 7,592
  • 13
  • 42
  • 68
  • I don't think that `INDIRECT` can take arrays. It even returns `#VALUE!` 5 times when `SUMPRODUCT` is used instead of `SUM` (which yields to 0 by the way) =/ – Jerry Nov 05 '13 at 11:12
  • @Jerry I'm open to suggestions how to replace this example with other functions. – Adam Ryczkowski Nov 05 '13 at 11:27
  • 1
    I don't know your exact values (the complexity of the references, but for your example, you could do something like `=SUM(INDIRECT(A1&":"&A5))` – Jerry Nov 05 '13 at 11:34
  • @Jerry Thank you for a good answer. Yes, that will work if the B1:B5 are actually in one row. In my setup they aren't. My illustration is really misleading, I'll update it now. You can see a related problem here: http://stackoverflow.com/questions/19788361/how-to-make-nested-array-computations-with-index – Adam Ryczkowski Nov 05 '13 at 11:55

1 Answers1

2

Finally I've found a way to solve it. As long as the values are on one sheet, we can take advantage of the fact, that when the second and/or the third argument to INDEX is an array, the result is an array as well:

enter image description here

It is a bit hackish, but it certainly works and doesn't need any VBA nor addons. And it is fast.

Adam Ryczkowski
  • 7,592
  • 13
  • 42
  • 68
  • Hmm, I've copied your example sheet exactly, but with that formula (using ctrl+shift+escape when entering), I still get a sum of 1 (or whatever is in B1). Any advice? I'm a relative Excel newbie so I may well be doing something wrong. – exscape Apr 04 '17 at 20:08
  • @exscape It is an array formula. Did you press ctrl+shift+enter after typing it in? – Adam Ryczkowski Apr 05 '17 at 17:06
  • 1
    Yep, I did (though I said escape in my previous comment, I did mean enter). – exscape Apr 05 '17 at 19:02
  • @exscape You are right; I also get "1". But if you omit the SUM function, you still get a proper array formula, that can display the consecutive 1,2,3,4,5 numbers... Weird, because I am pretty sure, that the sum used to work. – Adam Ryczkowski Apr 07 '17 at 08:59
  • I have un-accepted my answer, since it is misleading. I tried to fiddle a little, but I couldn't find a solution. The way INDEX handles (array) formulas with arrays in second and/or third argument is a little strange. – Adam Ryczkowski Apr 07 '17 at 09:08
  • 1
    I posted a very similar question to Superuser, and got an answer that solved the issue. In short, use =SUM(SUM(INDIRECT(A1:A5)) as an array formula (i.e. use Ctrl+Shift+Enter), and the sum shows up as 15. :-) Link: https://superuser.com/questions/1196243/simplify-a-sum-of-indirect-cell-values – exscape Apr 08 '17 at 13:36