Oakland Emp & Unemp.odsDear Folks–
I have a Calc worksheet with simple columns of numbers representing employment and unemployment numbers and rates. These numbers were copied as individual columns from Excel spreadsheets downloaded from the U.S. Bureau of Labor Statistics. They did not bring any formatting or formulas with them that I can identify by looking in the cells - just numbers. I am trying to take the difference of several pairs of these columns. I write out the difference in a cell in the usual way, e.g. “=M12-G12” (without the quotation marks) at the top of a column. I then grab the cell handle and pull it down to the bottom of the column. This fills the column with copies of the formula that differ in having the current row number in place of the original row number from the first entry in the column, as I expected. However, all of these formulas evaluate to the same number - the number in the original cell. This number is incorrect for all of the numbers but the original, and does not match the result of calculating the formula in the cell.
I have tried the following things which do not work:
Saving the file, closing it, and reopening it;
Cutting the column and pasting it back in the same place;
Cutting the column and pasting it back in the same place as formulas only;
Cutting one of the copied columns and pasting it back as numbers only;
Dragging from the bottom instead of the top;
Dragging to the right or left instead of vertically (again the formulas are correct but the number remains the value of the original cell);
Cutting the entire spreadsheet and pasting it into a different sheet;
Dragging the handle while holding down the Ctrl or Shift key;
Entering the formula in a different column;
Summing the numbers in two of these results columns sums the numbers displayed, not the correct results from the formula;
Placing the cursor at the end of the formula in the cell and hitting return;
Placing the cursor at the end of the formula in the formula bar window, or whatever it is called, and hitting return.
If I place the cursor in the formula bar, make some change in the formula, such as deleting the final “2” in the example I provided above, and then type the 2 back in again, so that the formula is unchanged, and then hit enter, the formula updates to the correct value. However, copying the updated formula using the drag handles has the same result as copying the original cell (but with the updated number).
Is there anything I can do to fix this? A spreadsheet that will not add two numbers is pretty useless. My best theory is that I pasted something invisible along with the numbers from the Excel sheets, but I would have thought that re-pasting the columns as numbers only would solve that.
All of this is being done on a new Dell with an i5 processor and Windows 7 SP1.
Any help or suggestions greatly appreciated. I have attempted to attach the spreadsheet in question below, but failed because my “karma is below 3”. Perhaps it will increase when I post this question, and I can add the file to a comment? Let’s find out.
I look forward to hearing from you.
Sincerely, andrewH
Update: Unfortunately after saving and reopening the spreadsheet I seem to have destroyed the reproducibility of the problem. However, after doing the trace in the sheet Test, but before saving, the values showing in, for example, cells Q18 and Q19 did not update and were still the same, while the same formula dragged down from U18 to u19 was producing correct results. After the save and reopen the Q formulas had still not updated. Note that before the trace saving and reopening had no effect.
Let’s see if I can now attach the workbook at least.
The excel sheets from which I originally copied these data series were downloaded from:
http://beta.bls.gov/dataViewer/view/timeseries/LAUCT065300000000005http://beta.bls.gov/dataViewer/view/timeseries/LAUCT065300000000006(http://beta.bls.gov/dataViewer/view/timeseries/LAUCT065300000000006)
and saved from those pages as XLS files.