Openpyxl creates a function that refers to a cell on another sheet

Question

I just started working with openpyxl a couple of days ago and its excellent library. However, the documentation is apparently rare for advanced features. I have a couple of questions.

• openpyxl seems to change the formula that I paste in lowercase, which results in an unknown link from excel.
• Also, I changed the name of the sheet to accommodate lower case and still found #NAME? error in the cell where the link was.

Can someone please show me how and where can I 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 the following:

 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 depends on the title of the NewSheet. Using quote_sheetname() controls this.