LET not performing function HYPERLINK

Question

I'm trying to use LET to avoid having to store intermediate calculations in two other cells. The function that it is supposed to run is HYPERLINK.

However, while Excel correctly calculates the value of the cell to hyperlink to, the HYPERLINK does *not* cause the calculated cell to become the selected cell i.e. as hyperlink is meant to function.

Here is a simplified version of my LET function, omitting the formula that calculates the values assigned to each of the names:

=LET( FirstMissingRow, 56, MissingColm, "B",
HYPERLINK( "#" & MissingColm & FirstMissingRow ) )

Here is what Evaluate Formula shows is happening:

..

..

Looks good to this point ... but sadly the next step results in:

Clearly, LET is forgetting the function HYPERLINK is being called!

If instead of LET I use helper cells to calculate the intermediate values, the HYPERLINK function does hyperlink to the correct cell. For example:

1. put FirstMissingRow formula/value in cell A1
2. put MissingColm formula/value into cell A2
3. enter the formula =HYPERLINK( "#" & A1 & A2 ) in cell A3

I get the same cell value text shown in A3 (#B56), and clicking on A3 causes the cell B56 to become the selected cell - exactly as hyperlink is intended to do.

• Why is my LET as coded not executing HYPERLINK function correctly?
• How can I get LET to work correctly?
• Or is LET only intended to calculate a value - MS documentation shows examples using "SUM" and "IF" to get a value, but has no "imitations" detailed on what calculations are allowed as the last parameter.

Thanks for the help.

Answer 1

Further testing...

If I remove the use of ADDRESS in my previous post of a few minutes ago, and replace it with purely A1 formatted cell references inside the IFS inside the LET, it works.

i.e. change the IFS(...) function that calculates FirstMissingColm to -

IFS( INDIRECT( "B" & FirstMissingDataRow ) = 0, "B",
     INDIRECT( "C" & FirstMissingDataRow ) = 0, "C",
     TRUE, "D" )

then LET allows the HYPERLINK to actually 'jump' to calculated reference on both Excel installations.

Note though that the use of ADDRESS within the HYPERLINK function still works with the INDIRECT I have there in the full coded version of this LET command.

So for now my 'full' cell function reads as follows -

=LET( FirstMissingDataRowLCL, 

          MIN( INDEX( FILTER( tbkWhRdgs[Row Nbr], tbkWhRdgs[Date of reading] = "",  MAX( tbkWhRdgs[Row Nbr] ) + 1 ), 1 ),

               INDEX( FILTER( tbkWhRdgs[Row Nbr], tbkWhRdgs[kWh reading] = "",      MAX( tbkWhRdgs[Row Nbr] ) + 1 ), 1 ),

               INDEX( FILTER( tbkWhRdgs[Row Nbr], tbkWhRdgs[Odometer reading] = "", MAX( tbkWhRdgs[Row Nbr] ) + 1 ), 1 ) ),

MissingColmLCL, MissingColm,

HYPERLINK( "#" & MissingColmLCL & FirstMissingDataRowLCL,     
             "click here to go to first incomplete" & CHAR( 10 ) &

             IF( INDIRECT( ADDRESS( FirstMissingDataRowLCL, COLUMN( tbkWhRdgs[[#Headers],[Is Row Hidden]] ), 1, 1 ) ) = "H", "the HIDDEN ", "" ) &

             "data cell (i.e. " & MissingColmLCL & FirstMissingDataRowLCL & ")" ) )

PS: I've put LCL for 'local' on the variables within LET. Hopefully can remove these when Excel fully fixed.

The reference FirstMissingColm is a named cell within the worksheet (& I've duplicated, hopefully short-term, the first variable in a helper cell so it can use the value). I want to use the newer function LET rather than have helper cells, hence why I've left the LET coded in a manner some might say "why bother then?".

Answer 2

Your posted LET function produces a functioning link for me on version 2312 Build 17126.20190. Which version and build of M365 are you on?

I'm on Microsoft® Excel® for Microsoft 365 MSO (Version 2401 Build 16.0.17231.20236) 64-bit

That earlier version is on my tablet where I usually use it.

My desktop machine is on Microsoft® Excel® for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20124) 32-bit. Note this is 32-bit, but tablet is using 64-bit.

Testing on the desktop, it behaves slightly differently. In the simple form, it works. but ..

• If I change the LET variables from just setting a value to calculating a value:
  1. calculating FirstMissingRow with the following MIN formula:
     MIN( INDEX( FILTER( tbkWhRdgs[Row Nbr], tbkWhRdgs[Date of reading] = "", MAX( tbkWhRdgs[Row Nbr] ) + 1 ), 1 ),
     INDEX( FILTER( tbkWhRdgs[Row Nbr], tbkWhRdgs[kWh reading] = "", MAX( tbkWhRdgs[Row Nbr] ) + 1 ), 1 ),
     INDEX( FILTER( tbkWhRdgs[Row Nbr], tbkWhRdgs[Odometer reading] = "", MAX( tbkWhRdgs[Row Nbr] ) + 1 ), 1 ) )
     it still works.
  2. adding calculating MissingColm with the following IFS formula which includes INDIRECT:
     IFS( INDIRECT( ADDRESS( FirstMissingRow, COLUMN( tbkWhRdgs[[#Headers],[Date of reading]] ), 1, 1 ) ) = 0, "B",
     INDIRECT( ADDRESS( FirstMissingRow, COLUMN(tbkWhRdgs[[#Headers],[kWh reading]] ), 1, 1 ) ) = 0, "C",
     TRUE, "D" )
     it fails as before.
  3. Returning the first calculation to an absolute value again makes no difference.
  4. If I substitute the calculation for the second to being a value from another cell rather than the "ifs..." HYPERLINK works once more.

So it seems a slightly higher level / 32-bit (one or both) of Excel changes the problem. Something about the formula for MissingColm is causing LET to have an issue, even at this higher Excel build level.

So I guess I have to wait for a fix to 64-bit, and a different (+ same?) one in 32-bit?

Answer 3

Your posted LET function produces a functioning link for me on version 2312 Build 17126.20190. Which version and build of M365 are you on?

I'm on Microsoft® Excel® for Microsoft 365 MSO (Version 2401 Build 16.0.17231.20236) 64-bit

Answer 4

Your posted LET function produces a functioning link for me on version 2312 Build 17126.20190. Which version and build of M365 are you on?