After copying formula to a new cell, how can I amend it?

BobL2 · September 12, 2019, 11:59am

When I copy a formal to a new cell, I wish to amend the cell that it points to. As soon as I amend anything in the formula the cell then displays the formula and not the result. I cannot even amend the original cell to show the address as $C$21, as when I do so, the cell will ow only display the formula and no longer the result!

An example: I copy =IF($‘Master Member List’.L39=“Y”,“Yes”,"") to a new cell, and then amend the address to (say) L41 then the cell now displays the formula and not the required “Yes”

erAck · September 12, 2019, 1:22pm

Might be the cell is formatted with a Text number format. Any input to a Text formatted cell will be entered as literal text content, not number or date nor formula. On the cell check menu Format → Cells… (Ctrl+1), tab Numbers, and set to Category Number, Format General and then edit the formula again (appending a blank and hitting backspace should suffice).

BobL2 · September 13, 2019, 9:03am

Thanks for that. I am still not sure what has been going on, but I have (at the moment) solved the problem.
On the cell check menu Format Cells the category was already set to Numbers, and the Format already set to general. Editing the formula again produced the formula in the cell not the calculated result. I changed the cell category to Text, and then back to numbers, and still the formula appeared in the cell rather than the result. I did that exercise three times, after which the cell magically gave the result rather than displaying the formula. However, copying the formula to another cell again resulted in the formula appearing! Copying and pasting three or four times does result in the cell finally giving the correct result, but what a palaver! Something in my system is not what it should be!

erAck · September 13, 2019, 9:34am

My wild guess: you have a clipboard destroyer (aka “manager”) running that interferes with how clipboard is supposed to work in that it converts everything to text instead of keeping the available formats, among them the Calc internal clipboard format. Those “managers” are notoriously known to break things.