1. Christian Wyglendowski
  2. XlsXcessive
  3. Issues
Issue #6 new

Shared formulas don't work when cells from different sheets are referenced in Excel 2010

Sven Hendriks
created an issue

When using a shared formula that references cells from differents sheets it doesn't work as expected when the document is opened in Excel 2010. Howver it does work in Open Office Calc 3.0.

In Excel 2010 the rows and columns referenced in the formula are not updated according to the cell the formula is put into.

Here's a snippet that creates such a document

{{{

!python

from xlsxcessive.xlsx import Workbook, save

workbook = Workbook()

sheet1 = workbook.new_sheet('Sheet1') sheet2 = workbook.new_sheet('Sheet2') sheet3 = workbook.new_sheet('Sheet3')

sheet1.cell('A1', 1) sheet1.cell('B1', 2) sheet1.cell('A2', 3) sheet1.cell('B2', 4)

sheet2.cell('A1', 10) sheet2.cell('B1', 20) sheet2.cell('A2', 30) sheet2.cell('B2', 40)

formula = sheet3.formula('SUM(Sheet1!A1,Sheet2!A1)', shared=True)

sheet3.cell('A1', formula) sheet3.cell('B1', formula) sheet3.cell('A2', formula) sheet3.cell('B2', formula)

save(workbook, 'formula_bug.xlsx') }}}

When you open the resulting document in Excel 2010 you'll notice that Sheet3 contains only 11s, because the formulas all reference cell A1 on Sheet1 or Sheet2 respectively, where they should reference A1, A2, B1 and B2 on both sheets respectively. When you open the same document in Open Office Calc the values are 11, 22, 33 and 44 as expected.

Comments (0)

  1. Log in to comment