Adding Hyperlinks to the names of sheets - the formula doesn't work

[Vitalite]

Hello!

I have found the formula that is supposed to create a hyperlink to the name of each sheet listed in a column. Please, take a look at the file I attach. There I describe how I created a list of names, using the macro (I believe it's a macro - I am not yet sure though). The formula INDEX() is used to get the name of each sheet. Works beautifully.

But now I would like each name to be a link to that sheet. The formula I found is this one below (details are in the attached file). To be honest, first I would like to learn reading it correctly, and then find out why it doesn't work.

Please Login or Register  to view this content.

- where excel_qns.xlsm is the name of the file;

- excel_qns is the name of the macro that gets the current workbook by using this formula

Please Login or Register  to view this content.

Here is what I understand in the hyperlink formula:

(1)

Please Login or Register  to view this content.

this formula is clear to me, and I now I know how it works;

(2) I presume the following part of the hyperlink formula means get the name of the certain worksheet, where the ending !A1 shall change as the formula being copied down

Please Login or Register  to view this content.

for example, if we are at the sheet 1, this part returns [excel_qns.xlsm]Summary

But then I don't understand why the IFERROR, or rather INDEX() part is repeated again.

I will be grateful for your help on understanding the hyperlink formula and on fixing the problem thus making hyperlinks actually work.

Thank you very much!