Formula Error - Constants

[thatlem]

I have a formula that works fine. I copied and pasted into another cell. After, the copied formula does not work. The formula evaluator indicates there is a constant in the equation.

I then built the same formula using the formula wizard, instead of typing it out, and the wizard pulls the correct value, but when I save the formula, it doesn't work, same as before - with the same error.

I am questioning whether there is a control character somewhere in the cell I can't see. I can't seem to clear it and not sure how to resolve.

HELP!!

 

[wiklendt]

when you copy a formula to a different cell, the formula updates any references to the changed location.

e.g., in cell A1 you create a formula which look like: =A2*10

if you then paste the cell A1 to B1, the formula will automatically update to: =B2*10.

is this what you mean?

 

[DCrake]

When copying formulas to different cells any cell references that need to remain constant need preceeding with a $ sign. such as

(A5+B5)*$A$1

 

[thatlem]

Actually the information you suggest does not address the problem. I am aware of the cell to cell relationship and fixing cell locations.

This would appear to be more related to something that I can't see - maybe a hidden control character in the cell itself. I have run into this problem mulitple times. An example would be to copy and paste a formula, recognize there is a problem, clear the contents then repaste the corrected formula. The new formula will work in other "not previously entered" cells, but in the cell that I originally pasted then cleared the formula, I get this error. I have verified the formula and removed any "space" characters before or after the formula, but continue to get the same error.

The only way I can clear the problem is to delete the cells, row, or column and start with a fresh cell. This can be a problem in a complex layout.

Any more ideas.

 

[chergh]

Might help if you provided more information. What the formula actually is or prefereably an example spreadsheet?

 

[wiklendt]

can't tell how to help you from that description.

if it is a bug in excel, have you tried searching for a hotfix form microsoft? is your excel program up-to-date with service packs and updates?

if not a bug, what is your formula? what do you expect to happen when you copy it (write out the formula you expect) and what is happening when you do it (write the formula that happens) - so, we need three formulas from you:

1) original
2) expected
3) observed

please provide all information that might help.