You can do this with the XlsxWriter Worksheet write_url() method using the internal: URI. See the XlsxWriter docs on write_url().

I found a way to do it.

Assuming one .xlsx file named 'workbookEx.xlsx' with two sheets named 'sheet1' and 'sheet2' and needing a link from one cell(A1) of the 'sheet1' to another cell(E5) of the 'sheet2':

from openpyxl import load_workbook

wb = load_workbook(workbookEx.xlsx) 
ws = wb.get_sheet_by_name("sheet1")

link = "workbookEx.xlsx#sheet2!E5"

ws.cell(row=1, column=1).hyperlink = (link)

The secret was the "#", Excel do not shows you but it uses the '#' for same file links, I just had to copy a same file link created in Excel to a Word document to see the '#'.

It is also possible to omit the filename, i.e. to link against a sheet of the active document just use: _cell.hyperlink = '#sheetName!A1'.

To name the link you just created, just set the cell value to the desired string: _cell.value = 'Linkname'.

The fully reproducible example provided below demonstrates how you would create a workbook, in which you are able to click on a particular cell and bring up another sheet in the same excel document.

In this example we use Xlswriter as the engine= parameter in pandas.DataFrame.to_excel(). This done to make use of some methods in the worksheet class of Xlsxwriter library, including worksheet.write_url()(link to docs).

import pandas as pd
import numpy as np

# Creating a dataframe 
df = pd.DataFrame(np.random.randn(10, 2), columns=list('AB'))
writer = pd.ExcelWriter("test.xlsx", engine='xlsxwriter')

df.to_excel(writer, index=False, sheet_name='data')

workbook  = writer.book
worksheet = workbook.add_worksheet('sheetWithLink')

worksheet.write_url('A1',  'internal:data!A1')
worksheet.write('A1',  "A link to another sheet")


writer.save()

Expected Output:

There are a couple of issues in your make_hyperlink() function and how you apply it. Here is a corrected version:

import pandas as pd

#create exmaple df
df = pd.DataFrame({'urls': ['https://www.google.com', 'https://www.youtube.com',
                            'https://www.twitter.com','https://www.reddit.com']})

#create function
def make_hyperlink(url):
    return '=HYPERLINK("%s", "Site Link")' % url


#apply function
df['hyperlink'] = df['urls'].apply(make_hyperlink)

#view df
print(df)

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Close the Pandas Excel writer and output the Excel file.
writer.close()

Output:

Showing a friendly name does not seem to work with write_url()'s 'internal:...' keyword.

It does:

import xlsxwriter

wb = xlsxwriter.Workbook('Wrap.xlsx')
ws = wb.add_worksheet('Test1')

wrap = wb.add_format({'text_wrap': True})

ws.write_url('A1', 'internal:Sheet1!A1', wrap,
             "A really long name here that does not wrap")
ws.write('B1', 'Bye')

wb.add_worksheet('Sheet1')
wb.close()

Output: