Use of Indirect with Row() as part of argument

  • Thread starter Thread starter JM
  • Start date Start date
J

JM

Hi,

I'm trying to enter an array function. This works:

INDIRECT("j"&2&":j31")

but this does not:

INDIRECT("j"&ROW(J2)&":j31")

I get a #VALUE error. Ultimately, I would like to have
the final function evaluate based on the row in which it
is located. I.e., I'd like to be able to simply use Row
() instead of "hardcoding" the J2 part.

Any help would be greatly appreciated.

Thanks,
JM
 
Hi
the problem occurs as your INDIRECT functions returns a range. e.g. the
following would work:
=SUM(INDIRECT("j"&ROW(J2)&":j31"))
or
=SUM(INDIRECT("j"&ROW()&":j31"))
 
Frank,

Thanks for the help.

However, I'm not sure I understand. Why does the first
one work and the second one not?

The difference between the two examples is in the
argument (both of which are text strings as I see it).

The next "step" in my function is to wrap this result in
the following way:

{=IF(INDIRECT("J"&ROW()&":J31")<>"","NOT EMPTY","EMPTY")}

This also returns a #VALUE error in all of the array
cells.

Can you provide more help or a reference that I can check
to understand what I'm missing? (I'm somewhat new to
using array functions.)

Thanks,
Jim
 
Hi Jim
maybe you can explain what you are trying to achieve. What is your
expected result from the formula below.
 
Hi Frank,

Sorry but I just got up (US East Coast).

To answer your question, this is part of a much more
complicated single cell array function. The ultimate
goal is to
1. take a column of data (let's say column J) that has a
label ("J label") at the top and that is of unknown
length.
2. for each cell in column K, create a single cell array
function that searches from the corresponding row in J
and down column B for the next 10 non-empty cells
3. then find the smallest 5 of those 10 cells

By experimenting, I found the following works for cell K2
but with shortcomings.

{=SUM(SMALL(INDIRECT("J2:J" & (SMALL(IF(INDIRECT
("j"&2&":j31")<>"",ROW(INDIRECT("1:30")),""),10)+1)),
{1,2,3,4,5}))}

The first problem is that it starts looking from J2 and
down rather than from the current row and down (for
instance, for J3, it should start at J3 and look for the
next 10 non-empty rows).

Since I will be sorting the rows, I can not "hardcode"
the row in for each cell. Rather the function must
adjust automatically when it is sorted to a different row.

I started by changing the &2& to &ROW()& and was
surprised that things didn't work right away again.

I'm still confused and could use any help. I'm going to
repost to see if I can spur any other people to help
explain the reason that the two text strings (&2& and &ROW
()&) result in different answers.

Thanks,
Jim
 
ROW(J2) returns an array INDIRECT cannot process...

Try...

INDIRECT("j"&CELL("Row",J2)&":J31")
 
Frank,

I just got a response that solved the original problem.
(Thanks to Aladin Akyurek.)

I think that since the Row() function was in an array
function, Excel tried to evaluate it for each array
element rather than just once as I had expected. So, my
syntax produced an array reference that changed with the
array element index --- not surprising that Excel doesn't
allow it. I'm not quite sure why Excel allows it for
your example that wraps the array in a SUM().

Thanks for your help. I noticed that you are "all over"
this board. I commend you for your service to the
community.

Best regards,
Jim
 
Hi Jim
also just say Aladins response. Glad he stepped in as I hadn't found a
solution for your issue until now :-)
 
Back
Top