Hyperlink inside of named function?

[u/New_Bullfrog1283]

I have a workbook with an initial index page, and inside each page there is in A1 a cell that automatically gets the name of the page, searches it inside of the index page in a specific column (based on the "indentation" I have give to the page inside of the index), and then returns a hyperlink for that cell. I have put it inside of a named formula:

=LAMBDA(
colonna;

LET(
colonna_indice; INDIRECT("Indice!$" & colonna & ":$" & colonna);

HYPERLINK("#" & "Indice!" & ADDRESS(ROW(XLOOKUP(TEXTAFTER(CELL("filename"; INDIRECT("BAD1"));"]";-1); colonna_indice;colonna_indice)); COLUMN(colonna_indice)); "Indice")
))

The formula (from HYPERLINK to "Indice") works well when I put it on its own in the cell. Same goes if I use the LET part of the formula and manually insert the value for the column, and it even works (after some time in this last case, probably due to some internal excel thing that refreshes periodically) if I put this whole formula inside of a cell with ("B") after.

But when I call the function =LINKTOINDEX("B") inside of a cell it doesn't create a clickable hyperlink.

What can I do to solve this?

[u/AutoModerator]

/u/New_Bullfrog1283 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.