Using FILTER in INDIRECT to calculate row value

[Ludwig]

I wanted the text in "friendly name" of a HYPERLINK to insert the calculated cell reference value you would be jumping to.

I used INDIRECT within the "friendly name" value. A simplified example of the formula is -

=INDIRECT( "B" & INDEX( FILTER( tbOdometers[Row Nbr], tbOdometers[Odometer] = "", MAX( tbOdometers[Row Nbr] ) + 1  ), 1 ) )

However, this results in a #VALUE! error.

But coding just the value inside INDIRECT in a cell, it works.

="B" & INDEX( FILTER( tbOdometers[Row Nbr], tbOdometers[Odometer] = "", MAX( tbOdometers[Row Nbr] ) + 1  ), 1 )

But if I put the INDEX( FILTER( ... portion of the formula in a cell directly and reference that within INDIRECT, that cell returns the correct value using INDIRECT.

Using Evaluate Formula, INDIRECT is being passed a value with braces round the string value (ie it's a 1-value array), rather than just the string value. Using index should bring just one value, not an array with 1 value surely?

But by referencing a helper cell this does not occur.

How can I get INDIRECT to function correctly using INDEX( FILTER(... as first shown without needing a helper cell?

 

[Ludwig]

Found a solution. While trying TEXT around the INDEX function didn't work, TEXTJOIN does - ie combine the "B" and INDEX value *then* use it as a string in INDIRECT.

=INDIRECT( TEXTJOIN( "", FALSE, "B",  INDEX( FILTER( tbOdometers[Row Nbr], tbOdometers[Odometer] = "", MAX( tbOdometers[Row Nbr] ) + 1  ), 1 )  ) )

Still have no idea why INDEX alone doesn't work, but at least TEXTJOIN does the job.