Jan 03 2021 11:19 PM
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 have a workaround?
thanks in advance
Aug 03 2021 12:12 AM
Aug 03 2021 04:47 AM
...to the fast :))
=hyperlink(a1:a3,b1)
or use Dynamic Hyperlinks
...see the attached file.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
Aug 03 2021 06:05 AM - edited Aug 03 2021 06:07 AM
Maybe try inserting an @ symbol:
=HYPERLINK(@A1:A3,B1:B3)
To make backward compatible perhaps use a relative name ThisRow:=R (in R1C1 notation):
=HYPERLINK(ThisRow A1:A3,B1:B3)
Note the Function Wizard can provide clues to the behaviour in such cases. Without using an intersection operator one sees arrays in the Friendly name and Result but not Link_Location:
Using bold type for arrays the function could therefore be written as:
HYPERLINK(Link_Location, Friendly_name)
Entering an array in Link_Location results in a nested array which gets truncated by the calc engine.
Compare with a function like CONCATENATE which only shows single value results:
CONCATENATE(Text1,[Text2],...)
Then arrays are handled in the normal way.
Not sure why HYPERLINK has this signature, perhaps it was originally designed to allow for text inputs longer than 255 characters in Excel 2003 versions and prior?
Nov 01 2022 12:21 AM
@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)
Nov 27 2022 03:12 AM
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.