Formulas proced with drag handle does not display correct values

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:

and saved from those pages as XLS files.

Nope, karma is still 1. This sort of discourages new posters from putting up information that may be necessary to allow others to answer their question, no?

You should have a enough karma now.

I attached the file using the dialog box, and it rather oddly put the link at the very beginning of my post. See above.

Looking at what you did, I can’t find anything wrong. Additionally copying formulas down work in general, I use this function very often.

Let’s have a look at your spreadsheet after you attached it. Make sure that no confidential data are included.

I would additionally do:
in 1. column 3 rows of simple figures like 10,20,30
in 2. column 3 rows of simple figures like 1,2,3
in 3. column the difference for the first row and copy down using the handle.
in a new Calc file
in the same Calc file but new sheet
in the same Calc file and on the trouble making sheet.

I also would use the “Trace Precedence” functionality to see what will be shown then.

Should you do this, it would be helpful to report the resutls.

Given 2 columns with data newly typed, addition of columns by dragging a formula cell in the same problem sheet has the same result described above.
Addition of cols in this way in a new worksheet in the same workbook also has the same result as above.
Addition of cols in this way in a newly created spreadsheet works fine.
In the newly created sheet in the original workbook, tracing either precedents or antecedents updates the values! & new formulas work! But existing ones formulas not updated.

Hi @andrewh, verify if they were pasted as text (left align) not as number, this can happen if your locale has comma as decimal separator while it is a point in US. If so, use Menu/Data/TexttoColumns to convert in numbers, column by column.

Unfortunately I do not remember if I right-justified the text after pasting, but there are no quotation marks in the cell, and the Numbers tab in the Format cells dialog box seems to claim that they are of format general. My Locale setting is Default - English (USA)

Please verify if Menu/Tools/CellConten/Autocalculate is on, if it doesn’t work, try a hard recalc [Ctrl+Shif+F9].

Just to thank maiosv - I had this same problem and the autocalculate fixed it - I couldn’t find it before - many thanks!