'HYPERLINK not working with particular sheet names

I have a workbook - lets call it Book1

Book1 contains 5 worksheets; Sheet1, Sheet1A, Sheet2, Sheet2A, Summary

Within the Summary worksheet there is a table which contains summary of the data held in the other 4 worksheet.

The first column in the table contains rows naming each of the 4 worksheets; Sheet1, Sheet1A, Sheet2, Sheet2A.

Using the excel HYPERLINK function I have created links in each of these rows so as to navigate directly to each particular worksheet, as follows:

=HYPERLINK("[Book1.xlsx]Sheet1!A1", "Sheet1")
=HYPERLINK("[Book1.xlsx]Sheet1A!A1", "Sheet1A")
=HYPERLINK("[Book1.xlsx]Sheet2!A1", "Sheet2")
=HYPERLINK("[Book1.xlsx]Sheet2A!A1", "Sheet2A")

However, only two out of the four hyperlinks work. The two which do not work are those which end with 'A'; Sheet1A and Sheet2A

All worksheets have been spelt correctly and I have experiment with the format to see whether changing to text or general made any difference, but it did not.

Please could could someone shed some light on why I am facing an error with Sheet1A and Sheet2A - my thoughts are that it is something to do with there being a letter after the number?

Thank you for your time.



Solution 1:[1]

If they're in the same workbook, then try adding the # & '

=HYPERLINK("#'"&"Sheet1A'!A1", "Sheet1A")

=HYPERLINK("#'"&"Sheet2A'!A1", "Sheet2A")

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mayukh Bhattacharya