Alright, I’ve had this problem in the past and I can never remember how I fix it.
I’ve been using the same spreadsheet for years (it’s a read only, I enter my numbers and then save it as another file so I can use the blank one again the next day). From time to time I have to make a small adjustment to a formula here or there or add a cell with a formula in it for one reason or another.
Today, I needed to add a few cells, so I go into my file, make the changes I want, add the cells I need, enter the formulas and one cell won’t work with the formula. The formula is “=VALUE(C28)” and that’s what’s displaying in the cell. I know Calc knows it’s a formula because when I click on it, it’ll highlight Cell C28 to show that it’s pointing to it, but it won’t put the value of C28 in the cell and it won’t not display the formula. I’ve tried everything, anyone else have any ideas?
Right now C28 is a blank cell, in the cell where I have the formula, the formula itself is being displayed. I need VALUE because I need a copy of the contents of C28 in another spot on the worksheet.
I’m trying to reproduce this in Star Office 9, which is OO with maintenance.
Putting in “=VALUE(C28)” or just “=C28” gives me a 0 if C28 is blank. VALUE is when the number in the cell is a quoted string - you shouldn’t really have to use it for just referencing a value anyway.
Are you sure you have the “=” there? Removing it does out “VALUE(C28)” in the cell.
ETA:
If I double click on the cell, then I get the formula and C28 gets highlighted. Do you have your mouse settings to automatically double click on things?
That can explain it.
I’m sure the = sign is there. Removing it turns it from a formula back to just regular text. Like I said, OO KNOWS it’s a formula, it just isn’t treating it like one…if that makes sense.
Also, I should mention that I use this exact same method all over this worksheet with no problems, it’s just that from time to time one cell gets stubborn.
Also, I’m not sure what you mean by “you shouldn’t really have to use it for just referencing a value”.
If you put something into C28, does your new cell change? Perhaps it’s confused by the empty cell?
I wonder if you tried to delete the cell contents and start over, if that would fix the problem? Select the cell (not the contents) and hit Delete - it should ask you if you want to delete everything, or just text or just formatting or whatever. Delete everything and retype your equation from scratch.
Did you look at double clicking? That displays the formula for editing, but it should go away when you hit return. It is the only way I could reproduce the problem.
What does just =C28 do? My reading of the VALUE function is that it converts a string representing a number into the number. Say you have two cells
1: =C28
2:=VALUE(C29)
If you enter a number 4 in C28 and C29, they will both show 4. However, if you enter “a” in both, 1 will show “a” while two will give an error - 502 in my version. I’ve never used value, but I suspect if you are constructing a number using string manipulation it is necessary.
I’m assuming that you’ve already tried the things mnemosyne suggested. If you haven’t, then you should.
I’m not at work right now so I can’t change anything but…deleting and re-entering doesn’t work. I even tried deleting it, then copying the cell above it (also a VALUE formula) and pasting it. I think that may have worked until I changed it to C28.
Putting something in C28 doesn’t make anything happen in the other cell.
Double clicking allows me to edit the cell, but upon hitting enter it goes back to the way it was, displaying the formula.
Also, I should mention that the cell properties of C28 and the cell in question are the same as the cells around them (which do work).
And again, to remind everyone, Calc is aware that this is a formula, when I click on it, it’ll highlight C28.
Grrr, eventually, something’ll click and it’ll start working again.