R0W() gives error in complex functions

[babbelut]

When i use the following function everything goes fine:

=INDIRECT(ADDRESS(ROW();12;4))

But when I use the following function, it gives an error:

=MATCH(1;('Mysheet'!L:L=INDIRECT(ADDRESS(ROW();12;4)))*1;0)

And when I change it to the following it goes fine:

=MATCH(1;('Mysheet'!L:L=INDIRECT(ADDRESS(112;12;4)))*1;0)


Why does ROW() work in the first function and not in the second, although the second is correct as it is working in function 3...



Thank you

[Jonmo1]

When you enter the formula with CTRL + SHIFT + ENTER
It is processed as an array.
The Row() function (in an array formula) will return an array result {112}
Indirect can't process that array result, and returns #Value! error

The first formula works because it's not an array formula entered with CSE.
So the Row function just returns the number 112, (NOT as {112})

Use the formula evaluation tool and see the difference in the result of the row() function between the two formulas.


To resolve, try the rowS function instead.
=MATCH(1;('Mysheet'!L:L=INDIRECT(ADDRESS(ROWS($A$1:$A112);12;4)))*1;0)

[Jonmo1]

I would also highly recommend NOT using entire column references like L:L in array formulas.
Limit it to your actual used area like L$1:L$1000

[XOR LX]

First of all, it's a terrible idea to use entire column references within an array formula.

Secondly, the explanation is a touch complex, though, in essence, it boils down to the fact that ROW is a function which has the property that it can return both a single value and an array of values.

Here, since you are employing the function with an array construction, ROW is coerced into expecting an array of returns, not just a single value. As such, this part (assuming we're talking about a formula in row 1):

INDIRECT(ADDRESS(ROW(),12,4))

is resolving to:

INDIRECT(ADDRESS({1},12,4))

and not to:

INDIRECT(ADDRESS(1,12,4))

the latter of which would evaluate further with no issues.

It may seem like a small difference - {1} vs 1 - but, when you then continue the evaluation, you see that the former resolves to:

INDIRECT({"L1"})

and, since you are attempting to pass an array to INDIRECT - albeit an array containing only a single value - this part errors.

To resolve this issue, you could use any number of means to coerce the return from ROW into a non-array datatype, e.g.:

INDIRECT(ADDRESS(SUM(ROW()),12,4))

which will this time evaluate to:

INDIRECT(ADDRESS(SUM({1}),12,4))

and then the SUM resolves this to:

INDIRECT(ADDRESS(1,12,4))

which can then be processed without error.

However, all this is a touch redundant, since the construction you are using is a poor one indeed. Not only is the unqualified ROW() an unrigorous choice in such set-ups (ROWS is far better), but volatile INDIRECT/ADDRESS constructions can almost always be replaced with better, non-volatile ones.

Regards

[babbelut]

Dear Jonmo1,

thank you for your explanation.
However, I used ROW() to return the current row, I cannot find how to return the current row with the ROWS() function.

[Jonmo1]

You're welcome.

However, I used ROW() to return the current row, I cannot find how to return the current row with the ROWS() function.

The way I just demonstrated ROWS($A$1:$A112) = 112

You only need to figure out the row# of the first cell you put the formula in
As you drag it down/up it will auto incriment.

Actually
ADDRESS(ROW();12;4)

Why not just put L112 ?

[babbelut]

As i understand that the function is just bad designed, i will give the whole explanation


I have two worksheets, let's say WS1 and WS2


In this screenshot you can see both WS's.

Image 1.png

What I want to do is create a hyperlink in column E of WS1 (next to every person)
This link takes me to WS2, to the row where it finds the month, the name and the firstname.

Every formula in E needs to be the same, this is what makes it difficult...

How my function is build now:

=HYPERLINK("#'WS2'!"&ADDRESS(ROW(INDEX('WS2'!A:C;MATCH(1;('WS2'!A:A=C6)*('WS2'!B:B=D6)*('WS2'!C:C=F6);0);0));1);"My link")


In F6 I have the following code to find the month...
=INDIRECT(ADDRESS(SUMPRODUCT(MAX((INDIRECT("B1:"&ADDRESS(ROW();2;4))<>"")*ROW(INDIRECT("B1:"&ADDRESS(ROW();2;4))))); 2; 4))


If something is not clear, please ask.

[Jonmo1]

It looks like the formula's purpose is to find the last non blank cell in column B (up to the row the formula is entered in)
Is that right?

Try this in F6 (not required to enter with CSE)

=LOOKUP(2,1/($B$1:$B6<>""),$B$1:$B6)


If you insist that you need to use the row function to faciliate finding the end row..

=LOOKUP(2,1/($B$1:INDEX($B:$B,ROW())<>""),$B$1:INDEX($B:$B,ROW()))

[babbelut]

Jonmo1, that is right.

But I have simplified my formula to show it here, actually i don't want to show the value of that cell but from another column.
The real excel looks more like this:

Image 2.png

So it will look in column a what the last non blank cell is and then return the value of column B of that row...


Edit: F6 is also a workaround for the moment, i actually want that part in the hyperlink function too...
Edit 2: I cannot work with B6 or any other row number, as all functions on all rows will be the same. I have to work with row() or something else to pass the row.

[Jonmo1]

Can you attach an actual workbook?
Pictures don't help alot, we can't copy paste values/formulas from a picture into our own workbooks for troubleshooting.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Jonmo1]

So it will look in column a what the last non blank cell is and then return the value of column B of that row...

Then change
=LOOKUP(2,1/($B$1:INDEX($B:$B,ROW())<>""),$B$1:INDEX($B:$B,ROW()))
to
=LOOKUP(2,1/($A$1:INDEX($A:$A,ROW())<>""),$B$1:INDEX($B:$B,ROW()))

[Jonmo1]

I don't understand this part.

I cannot work with B6 or any other row number, as all functions on all rows will be the same.

Why?

[babbelut]

The formula will be entered with software, which is only able to put in the same formula every time...

I have included an example xls.
Where "LINK" the formula has to come.
When I click in the link it has to go to SheetB, where:

SheetB:ColumnC = SheetA:ColumnL of last row of Column A above the current row
and
SheetB:ColumnL = SheetA:ColumnC


I hope you understand what i am trying to do.

[Jonmo1]

I hope you understand what i am trying to do.

Not really, but I understand what the formula was trying to do.
It would help if your attached file included the formulas you attemted, and realistic data that they would work off of.

But anyway..

This failed because of the row() function returning it's result as an array {6} As described before.
=INDIRECT(ADDRESS(SUMPRODUCT(MAX((INDIRECT("B1:"&ADDRESS(ROW();2;4))<>"")*ROW(INDIRECT("B1:"&ADDRESS(ROW();2;4))))); 2; 4))

This formula will produce the same result that formula was trying to accomplish
=LOOKUP(2,1/($A$1:INDEX($A:$A,ROW())<>""),$B$1:INDEX($B:$B,ROW()))

Except I changed it to look in column A for the last non blank, instead of B which you had in your original formula.
Because you said so..


Also, XOR LX provided a simple solution to make your original formula work.
Wrap the Row() function in the SUM function.
SUM CAN handle the array result of the row function, and return it as a non array number.
In your original, INDIRECT would recieving a reference like INDIRECT({"B1"}) Insetad of INDIRECT("B1")

So replace ROW() in your formula with SUM(ROW())