Hyperlink function do not support Dynamic Arrays

Old thread here, but still without a fix.

Seems you can either make it dynamic for the links or for the friendly names but not both at the same time without the help of other functions.

This solution is based in the case of building a table contents only with formulas (so hyperlinks to other locations in the workbook, but should be easy to adapt for urls).

Say you have a tab call contents where you have a dynamic formula in A1 that spills the names of the tabs where you want to navigate to. Then you can refer to A1# with HYPERLINK but providing a single friendly name that should never match a tab name. Then you can use substitute and refer to another column with friendly names, in my case, column A again. If you use another column make sure it's the same size:

=SUBSTITUTE(HYPERLINK("#'"&A1#&"'!A1","Never_use_this_as_a_tab_name"),"Never_use_this_as_a_tab_name",A1#)

If you are wondering how to build that spill formula to get the list of tab names, refer to this Acces Analytics video in youtube

https://www.youtube.com/watch?v=3n6gGox4pSk&lc

Forgot to mention: this will not give you the hyperlink format for all the spilled rows, only for the first row.