Forum Discussion
Hyperlink function do not support Dynamic Arrays
Hi, tried a very simple sheet where cell a1..a3 are links and cells b1..b3 are friendly names the formula hyperlink(a1:a3,b1:b3) shows the hyperlinks correctly but not the friendly names anyone ha...
lori_m
Wrap the hyperlink function around an index array function.
for example, where SHEETNAMES is an Array of the form $A$1# containing an array of sheet names for the workbook:
=HYPERLINK(INDEX(CONCATENATE("#'",SHEETNAMES,"'!A1"),1,XMATCH(SHEETNAMES,SHEETNAMES,0)))
this however wont create the automatic formatting for a hyperlink, you may need a conditional format to achieve this.
I can't answer why this works, but it worked for me (Office 365)
That's an interesting observation. For me this version also works fine:
=HYPERLINK(CONCATENATE("#'",SHEETNAMES,"'!A1"))but replacing the SHEETNAMES range reference with an array such as {"Sheet1","Sheet2","Sheet3"} doesn't preserve spilled links. The INDEX suggestion also doesn't solve the posted question for me i.e. spilling friendly name.