#REF! unappreciated in formula

English
, , , ,
#1

Disclaimers:

  • “send feedback” menu item takes me to bug report site, but following behaviour appears to be by design
  • not attempting to compare directly to other spreadsheet products, although welcome if relevant
  • if this is just a case of “not there yet,” that’s cool

Briefly, I expect a bad formula cell result to report bad news, e.g., <#REF>, but not transmorgriphy the formula. Is there a way to avoid transmorgriphication? I have hope that I’m uninformed or ignorant of best practice for pasting a formula.

If I copy and paste a formula from a separate sheet or file, the pasted formula replaces all the cell references with #REF!. Shouldn’t pasting a formula to a new sheet keep cell references (with associated bad result), reporting an unusable formula and retain it, like a string with an output error? Alternatively, replace relative with absolute references or add a file and sheet name somehow? Cell selection copy and paste on a common sheet calculates a result, for better or worse, but the formula is intact with any relative cell reference changes. Any badness is in the cell result. Both absolute and relative references on a common sheet behave as expected.

I expect same-sheet cell relationships to be retained, no matter what sheets are involved. The reason I’m using copy/paste is to save on typing. Why should any output error wipe my input? Obviously, <=AVERAGE(#REF!#REF!:#REF!#REF!)> is all wrong all the time, but it contributes nothing to help me resolve bad references. If the program knows there’s something wrong with a cell reference, why not just annotate it somehow, not trash the formula?

Bonus round:
Couldn’t find any interface for relative/absolute address switching or automation. Help page <shift+F4> doesn’t appear to do anything with Mac OS LO v6.3.5.2. Anybody know where that functionality is enabled?

#2

skyhook,

Can you share the original formula?

Have you tried to edit the original formula, copy the content, edit the destination cell and paste de content?

Thanks for share LO version.

#3

The typical case where this happens is when your original formula uses relative addressing, and you pasted it to a cell where the relative argument position is “no man’s land” (usually left of column A or above row 1). Might this be the case for your issue?

#4

LG:
The example above is real, a range average pasted to a new sheet:
=AVERAGE(D13:D62)
becomes
=AVERAGE(#REF!#REF!:#REF!#REF!)
Yes, it’s simple, but I’m questioning the general behaviour, or my misuse of it.
Yes, I can copy cell contents one cell at a time, but not worth the effort expended.

This example is one cell of many. I just wanted to reformat some data and it became very clumsy. My best workaround might be to duplicate new sheets and update the data, so nothing formulaic actually changes. I’m not stuck, just surprised. I also assume there is a named range feature I could use, but I haven’t looked into that yet.

#5

KE: Ouch, you are correct. I’ve made mistakes in my testing.

  • I can fail a formula in a common sheet, exactly as you described

  • using absolute references, the paste sheet retains the formula, result falling as it may

  • select/copy a range of formula cells, each acts according to its own formula, as expected

  • if I enter a cell, sheet and file references are added for me

Still wondering where the switches are for relative/absolute, if at all. Now extrapolated, the automatic use of sheet/file names without reaching inside the cell. They work automatically if I’m editing a formula, but I haven’t found any other way, manual entry notwithstanding.

Thanks; my long-winded question appears to be self-induced. Want to write it up that way and I’ll check it off?

#6

On a laptop the F keys are sometimes stolen for music playback etc. in those circumstances you might have to use the Fn (function) button first. On current LO absolute addressing is now just F4 for Win (used to be SHIFT+F4 once), so try that and if doesn’t work try Fn+F4

#7

EA: not Fn this time. On a wide, clicky desktop.
You are correct! On this Mac, F4 alone cycles through each address component combination, as described on the help page.

Also, I’ve discovered it under Sheet/Cycle Cell Reference Types, but F4 appears to be selection and not sheet specific, with a misleading menu location. Also, unrelated to defaults. A fun groovy is that each cell in a multi-cell selection cycles from its beginning format, should they be mismatched.

Still looking for defaults.

#8

Okay, I guess everyone went to bed; thanks LG, KE, EA:

To answer my own question, pasting formulas works much as expected and I made mistakes testing samples.

Relative and absolute references survive copy-paste as long as they refer to available cells in the destination sheet, both common sheet or elsewhere. Sheet and file references work in their own fashion. If there is an outlier, it would be a relative cell reference that doesn’t exist in the paste location, for example, off the grid.

I will start the default nature of reference settings in a new post.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.