I needed to tally votes from a simple poll for determining priorities of some topics. Several other people need to analyze the results; since they are from different organizations, I decided to use Google Spreadsheets.
Instead of going through the whole process of how I arrived at the bug, I’ll cut to the chase and just explain the problem:
If a Google Spreadsheet uses references to another spreadsheet, sorting the spreadsheet does not adjust the referenced cells correctly.
To demonstrate the problem, here’s a few screenshots:
Note that cell A3 uses formula =’Topics’!A3 in order to display the value from the corresponding cell of the Topics spreadsheet.
After sorting on column D, notice that columns C & D (in green rectangle) are changed, but columns A & B (in red rectangle) are not. The result of the sort is incorrect and useless at this point because Topic A, Choice 1 is not truly the top rated item.
To demonstrate a bit further, I changed hard-coded the values in column A rather than have them reference the values in the Topics spreadsheet. The result of sorting after this change looks like:
Here we see that columns A, B & C have changed correctly, but column B has not because it still uses references to the Topics spreadsheet. (Note the value of cell A3 shown in the Formula bar just above the spreadsheet)
So, is this a bug or not? I would count it as “unexpected behavior,” but I guess Google will have to determine whether they want fix this or not. I got the results I expected from Excel, however, as you can see here:
If you’re interested to tinker with or investigate this issue, you can copy the Excel spreadsheet, import it into Google Docs and tinker away. Caveat: You may have to set the reference formulas in the Results spreadsheet to point to the appropriate cells in the Topics spreadsheet. I don’t think Google converts the references to hard values during import, so verify it beforehand.