Spilling an array with hyperlinks

Copper Contributor

I want to spill an array that includes hyperlinks to various places in the workbook. In principle something like this:

 

=MAKEARRAY(3; 3;
   LAMBDA(Row; Col; 
       HYPERLINK("#R" & Row & "C" & Col; "click")
   )
)

 

But something is off. A 3x3 array is spilled with clickable hyperlinks in each cell - but the hyperlinks all take me to A1 (R1C1). Clicking on the bottom right cell should take me to R3C3, for instance. What's wrong?

3 Replies

@YeBoldeSquirrel 

Your formula works for me (Excel for the Web / English regional settings).

These formulas all lead to the same result:

=MAKEARRAY(3,3,LAMBDA(Row,Col,HYPERLINK("#R"&Row&"C"&Col,"click")))
=LAMBDA(Row,Col,HYPERLINK("#R"&Row&"C"&Col,"click"))(SEQUENCE(3),SEQUENCE(1,3))
=LAMBDA(Row,Col,HYPERLINK("#R"&Row&"C"&Col,"click"))({1;2;3},{1,2,3})
=HYPERLINK("#R"&{1;2;3}&"C"&{1,2,3},"click")

 

Thanks! This is surprising, I just tested it with Excel Online, and indeed it works as intended. But the same thing doesn't work properly when opened with standard desktop Excel! Now I'm assuming some sort of bug here...
I now tested this with Excel on the iPad, and it works as intended - just as in Excel for the Web. There clearly seems to be a bug in desktop Excel when handling hyperlinks in arrays.