https://bz.apache.org/bugzilla/show_bug.cgi?id=60517
--- Comment #3 from Mark Murphy <[email protected]> --- (In reply to Javen O'Neal from comment #1) > Could you include a code snippet of what you're doing, including any > manipulations you are doing to the stored formula string and how you're > getting the formula string. > > FWIW, when I open your attached XLS file in LibreOffice, the following > formula appears in D17: > =SUM($'1003'.D28:$'1856'.D28) > > When I use LibreOffice to save the file as XLSX, I get: > =SUM('1003':$'1856'.D28:D28) > > And after converting to XLSX, the value saved in sheet1.xml is: > <c r="D17" s="19" t="n"> > <f aca="false"> > SUM('1003':'1856'!D28:D28)</f> > <v> > 102320067</v> > </c> > > It may be possible that both versions are correct. > For example, 'Sheet1'!A1 and Sheet1!A1 are both correct. In general, single > quotations are needed whenever a character in the sheet name could cause the > formula to be parsed differently (whitespace, punctuation, sheet name looks > like a cell reference). I would assume that '1003:1856' would not be correct > as that would imply that there is a sheet named "1003:1856". I did not know a range could look like this. I did some testing in Excel 2016 (Windows), and it appears that =SUM('1003:1856'!A1) is a valid range syntax where there is a tab named 1003 and another tab named 1856. All tabs physically between tabs 1003 and 1856 will be included in the sum. So if I created a tab named Sheet4 and put a value in cell A1, that cell is included in the sum only if I move tab Sheet4 to a position between tabs 1003 and 1856. This formula is maintained, including single quotes, if I save and reopen in XLS or XLSX format. If I add another tab Sheet5, and then make the formula =SUM('Sheet4:Sheet5'!A1), Excel changes it to =SUM(Sheet4:Sheet5!A1) -- You are receiving this mail because: You are the assignee for the bug. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
