=SUM($januari.I2:$januari.I999999) becomes =SUM($januari.#REF!#REF!:#REF!#REF!) after re-opening

I have the following in a cell

=SUM($januari.I2:$januari.I999999)

It works (the sum shown is correct) but as soon as I save it and re-open it I get

=SUM($januari.#REF!#REF!:#REF!#REF!)

image description

example.xls

Is it me doing something wrong? Or is this a bug?

Hello,

format .xls has a limit of 65.536 rows and 999.999 is clearly larger than that;-). Save as .xlsx (or even better as .ods). The problem will be created on saving such file and not on opening.


BTW: You probably ignored the warning:

This document may contain formatting or content that cannot be saved in the currently selected file format “Excel 97–2003”.

Hope that helps.

I never saw that warning :slight_smile:

It works for xlsx but it did not with “save as”. I had to recreate the sheet and could then copy it all over.

Thanks. .

I never saw that warning :slight_smile:

The only reason I could imagine is: You have set .xls to be the default format for spreadsheet documents at: Tools -> Options -> Load/Save -> General -> Section: Default File Formats and ODF Settings -> Drop down: Document type: Spreadsheet -> Dropdown: Always save as: Excel 97-2003 (.*xls)

=SUM(januari!I:I)