Problem using INDIRECT in Array Formula with changing row

  • Thread starter Thread starter The Shaffer s
  • Start date Start date
T

The Shaffer s

I'm having a problem with an array formula. A portion of it returns the
last non-blank column on a row. Instead of hardcoding the row, I want it in
the formula, relative to the current row.

For example, the ROW()-1 part, if replaced with a real row, works fine. When
the ROW()-1 is in, I get #VALUE!

This fails:
=MAX(COLUMN(1:256)*(OFFSET(INDIRECT("A"&ROW()-1),0,0,1,256)<>""))

This works:
=MAX(COLUMN(1:256)*(OFFSET(INDIRECT("A1"),0,0,1,256)<>""))

Any help greatly appreciated.

Ken Shaffer
 
The Shaffer s said:
For example, the ROW()-1 part, if replaced with a real row, works fine.
When the ROW()-1 is in, I get #VALUE!

This fails:
=MAX(COLUMN(1:256)*(OFFSET(INDIRECT("A"&ROW()-1),0,0,1,256)<>""))

This works:
=MAX(COLUMN(1:256)*(OFFSET(INDIRECT("A1"),0,0,1,256)<>""))
Click to expand...

This is a subtle syntactic issue. ROW() and COLUMN() always return arrays,
even when they return only one number. This doesn't cause a problem in the
INDIRECT call, because INDIRECT can take an array 1st argument and return
what's treated as an array of ranges. OFFSET, on the other hand, can't cope
with arrays of ranges as 1st argument.

While it may seem silly, try

=MAX(COLUMN(1:256)*(OFFSET(INDIRECT("A"&SUM(ROW()-1)),0,0,1,256)<>""))
 
If I understand what you're saying Harlan, this *shouldn't* work,
BUT ... it does !

=OFFSET(INDIRECT("A"&ROW()-1),,1)

So please, what am I not following?
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


The Shaffer s said:
For example, the ROW()-1 part, if replaced with a real row, works fine.
When the ROW()-1 is in, I get #VALUE!

This fails:
=MAX(COLUMN(1:256)*(OFFSET(INDIRECT("A"&ROW()-1),0,0,1,256)<>""))

This works:
=MAX(COLUMN(1:256)*(OFFSET(INDIRECT("A1"),0,0,1,256)<>""))
Click to expand...

This is a subtle syntactic issue. ROW() and COLUMN() always return arrays,
even when they return only one number. This doesn't cause a problem in the
INDIRECT call, because INDIRECT can take an array 1st argument and return
what's treated as an array of ranges. OFFSET, on the other hand, can't cope
with arrays of ranges as 1st argument.

While it may seem silly, try

=MAX(COLUMN(1:256)*(OFFSET(INDIRECT("A"&SUM(ROW()-1)),0,0,1,256)<>""))
 
That worked. Go figure.

I thought OFFSET's first argument could be a range. Unless, ranges and
arrays are different.
While it may seem silly, try

=MAX(COLUMN(1:256)*(OFFSET(INDIRECT("A"&SUM(ROW()-1)),0,0,1,256)<>""))
Click to expand...

Ken Shaffer
 
Back
Top