Problem using INDIRECT in Array Formula with changing row

[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

 

[Harlan Grove]

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)<>""))

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)<>""))

 

[RagDyer]

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!
-------------------------------------------------------------------

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)<>""))

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)<>""))

 

[The Shaffer s]

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)<>""))

Ken Shaffer