Google Spreadsheet Sorting Bug

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:

Google Spreadsheet - Initial State
Google Spreadsheet - Initial State

Note that cell A3 uses formula =’Topics’!A3 in order to display the value from the corresponding cell of the Topics spreadsheet.

Google Spreadsheet - Incorrect Sort Behavior
Google Spreadsheet - Incorrect Sort Behavior

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:

Google Spreadsheet - Partly Correct Sort Behavior
Google Spreadsheet - Partly Correct Sort Behavior

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:

Microsoft Excel - Correct Sort Behavior
Microsoft Excel - Correct Sort Behavior

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.