openpyxl create a function that references a cell in another sheet

Question

I just started working with openpyxl a couple of days ago and its a great library. However, the documentation seems to be sparse for advanced features. I have a couple of issues.

1. openpyxl seems to change the formula that I insert to a lower case which results in an unknown reference from excel.
2. furthermore, i changed the name of the sheet to accomidate the lowercase and still found a #NAME? error in the cell where the reference was at.

Can someone please show me how or where to find out how to reference a cell from another sheet in openpyxl

import openpyxl.Workbook
wb = Workbook()
ws = wb.get_active_sheet()
#shows up lowercase with name error in excel
ws.cell('A1).value = "$'Sheet'.E7 + 123"
#still shows a name error in excel
ws.cell('A2').value = "$'sheet'.E7 + 123"

Answer 1

Try this:

from openpyxl import Workbook
wb = Workbook()

ws = wb.create_sheet()
ws.title ='NewSheet'
ws.cell('E7').value = 7

ws = wb.create_sheet()
ws.cell('A1').value = "=NewSheet!E7 + 123"

wb.save( filename = 'temp2.xlsx' )

Answer 2

from openpyxl import Workbook, utils
wb = Workbook()

ws = wb.create_sheet()
ws.title ='NewSheet'
ws.cell('E7').value = 7

ws = wb.create_sheet()
ws.cell('A1').value = f"={utils.quote_sheetname(ws.title)}!E7 + 123"

wb.save( filename = 'temp2.xlsx' )

The problem with the previous answer is that it's dependant on the title of the sheet being 'NewSheet'. Using quote_sheetname()controls that.