Indirect range "can't be used as a single value"?

Question

Level 1

4 points

Indirect range "can't be used as a single value"?

Hello!

I'm trying to create a range selection dynamically, and I'm getting stuck on Indirect.

If I use the following formula in a cell, I get as text output what _seems_ like a nice little cell reference:

"Monthly Table::"&ADDRESS(MATCH(A8,Monthly Table::A,matching-method),3,addr-type,addr-style,table)&":"&ADDRESS(MATCH(A8,Monthly Table::A),14)

returns:

Monthly Table::$C$19:$N$19

But, when I fold that first formula into an Indirect function, like so:

INDIRECT("Monthly Table::"&ADDRESS(MATCH(A8,Monthly Table::A,matching-method),3,addr-type,addr-style,table)&":"&ADDRESS(MATCH(A8,Monthly Table::A),14))

I get the following error:

"The Range Monthly Table::C19:N19 can't be used as a single value"

What terribly obvious thing am I most likely missing?

Thank you for any help.

Posted on Sep 24, 2024 4:09 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

28,006 points

Sep 24, 2024 6:14 PM in response to iethatis

It is nothing to do with INDIRECT. If you type Monthly Table::C19:N19 as an actual reference in a cell you will get the same error. If you want to use a range as a single value, use INDEX(your_range, 1). Numbers has no problem with vertical ranges like B2:B10 (it will accept it and give you the first value) but it does not like horizontal ranges like B2:E2 or 2-dimensional ranges. I have no idea why one is okay and the others not. An oversight or bug, perhaps.

Reply

Answer 2

iethatis Author

Level 1

4 points

Sep 26, 2024 2:52 PM in response to Badunit

Thank you, Badunit! This weirdness was confusing. In the end, I found a much simpler way to do things because I stopped being stupid about concatenation.

Reply