Reference in formula changes to #REF!

[Gerti]

Hello,

I had a problem with Calc (OO 2.3.1 - Windows XP).
Sorry, if I use the wrong words, but I'm from germany and do not know the exact english words for the sheets, tables, etc.

I had one file with lots of sheets and each sheet contains lots of tables and formulas.
Now it happens again and again with some tables (copied from another document) that the formula changes from "=HE16*C16" to "=HM16*#REF!16" and that the fields contain also #REF! instead of the expected value.
Directly after copying these tables from the other document, everything is fine, but sooner or later, they are getting corrupted (#REF!), also if I'm not even working on the sheet which contains these tables.

The first row of these tables are still okay if this happens. They are containing for example "=HE15*C15", all other rows are containing "=HE16*#REF!16, =HE17*#REF!17, =HE18*#REF!18 and so on". Also all other tables on the right of the first affected table (there are lot's of tables in a row), are also affected with the same beaviour.

Any ideas?

Best regards,
Michael

[Villeroy]

Do this little experiment on a new sheet:
C3 =B2 (one row above, one column to the left)
copy C3 to B2
now B2 =A1 (one row above, one column to the left)
copy B2 to A1
now A1 =#REF!#REF! (no such row above, no such column to the left)

See also http://user.services.openoffice.org/en/ ... =75&t=2443

[Gerti]

Hi!

Thanks for your answer, but did this really fit my problem?
In your example, B2 will be replaced with #REF! in my example it will be replaced with #REF!2...so it seems to be a little different. And, as far as I can believe to my colleague, this error also happens, if she is doing nothing within the sheet.

I refered to your link, but didn't exactly find a solution for that...
Is there any way to solve it?

My colleague is working now again with MS Excel (the bug doesn't happen with Excel) and is blaming me again for forcing her to make use of OpenOffice.

Best regards,
Gerti

[TerryE]

Gerti,
Is there any way that you can create a simple test ODT which shows this and attach it here as a reply? A concrete example is always easier to diagnose.

[Villeroy]

From the viewpoint of F20 (row 20, column 6) the relative reference =C16 (row 16, col 3) refers to the cell 4 rows above and 3 columns to the left.
When you copy reference =C16 from F20 to G21 it becomes =D17 in G21, still referencing the cell 4 rows above and 3 columns to the left.
When you copy reference =C16 from F20 to F1 it becomes C#REF because it can reference 3 columns to the left (col C) but not 4 rows above (row -3).
When you copy reference =C16 from F20 to A20 it becomes #REF16 because it can reference 4 rows above (row 16) but not 3 columns to the left (column -2).

The solution to you problem may be this:
Copy the formula =HE16*C16 to the same cell where it used to be on the source sheet, then move it to another place.