Search code examples
pythonexcelpandasformulaopenpyxl

openpyxl: how to refer a cell in another sheet inside a combination of formula using OFFSET and INDIRECT functions

I am trying to automate quiz grading for trainees. The steps we follow are as follows:

  1. pull quiz data from ODKaggregate server as a CSV file and save it in xlsx format
  2. Load the data in python (I am using Pycharm IDE)
  3. Create a new sheet for every question in the quiz
  4. Automate calculating the average scores of every question inside the xlsx form

I am successful in addressing steps 1-3. In order to automate the calculation of average score in every sheet, I am using the AVERAGE, OFFSET and INDIRECT excel functions. However, I can not refer to a cell coordinate in another sheet inside the INDIRECT function, it is returning SyntaxError: invalid syntax . Here are the lines of code I have tried:

import pandas as pd
import openpyxl
from openpyxl import load_workbook, Workbook
quiz_excel=pd.read_csv (r'path_to_csv_file.csv')
quiz_excel.to_excel (r'path_to_xlsx_file.xlsx', index=None, header=True)
wb: Workbook = load_workbook (r'path_to_xlsx_file.xlsx')
sheet = wb['Sheet1']
max_row = wb.active.max_row - 1 # the '-1' is to ignore the column heading'

for i in range(max_row+1):
    wb.create_sheet('Q')

wb['Q1']['B2'] = "=AVERAGE(OFFSET(INDIRECT("'Sheet1'!AC1",1,0,1,3))" # i am getting the error from this line of code.

can anyone help me with the problem, please. your help is very much appreciated!!

Solution

  • Thanks a lot @balmy, escaping the internal " with " solve the problem. I was supposed to edit the code to make work as: "=AVERAGE(OFFSET(INDIRECT("'Sheet1'!"& "AC1"),1,0,1,3))