MrExcel Publishing
Your One Stop for Excel Tips & Solutions

cell works out the calculation and places the value in the cell with no trace of its workings....?


Posted by Phil on June 04, 2001 7:32 AM

Having a problem

Everytime i make a formula e.g =B1+B1 it writes the answer instead of the fomula =B1+B1.... this means that i can't drag the formula down through collumn A..


Posted by Eric on June 04, 2001 8:02 AM

I hope im not misunderstanding your problem. If you copy a formula down to the next cell it should "float" the reference. For example, if in column (C) row (1) you wrote the formula =b1+b1 then the display within the cell (C1) should give you the result. If you copy that cell down one row (to C2) you should get a formula that says =b2+b2, likewise if you copied over one column to (D1) the formula should change to =c2+c2.
Your issue may be what is displayed in the cell verses the cell's actual content. If you highlight the cell and then look in the formula bar (top of the screen) it displays what is actually in the cell, while the cell itself displays the results of the formula.
I feel like this explanation is way too basic and i've misinterpreted your question. Please clarify if I have.

Posted by Joe Was on June 04, 2001 9:17 AM


If you are wanting the same formula in other cells without the address changing?
Temporaruly change the formula to text by adding " ' " in front of it and copy-paste. Like '=B1+C1...
Then edit the " ' " out.
Its a pain but short of a macro combo box for non-edit pastes its the only way I know?

Posted by Becky on June 04, 2001 10:47 AM

If you make the cell refernces absolute by add the dollar sign, teh reference won't change as the formula is copied. Example: =$B1+$C1 in this example the column reference will not change. Or
=B$1+C$1 in this example the row reference will not change.

Posted by Joe Was on June 04, 2001 11:03 AM

Becky is right if you want to reference an absolute address.

If you want to use the "=" as the start of a Text statement start it with " ' " Excel will not display the leading " ' "
If this does not help you can you restate your problem? JSW

Posted by Phil on June 05, 2001 1:16 AM

sorry i mustn't hav explained my self well enough...

but, It's fixed itself now..... (however i wouldn't mind know y i did it)

what was hapening was if i typed in =1+1 it wrote the value 2 in the cell (as it is supposed too) & it wrote 2 in the formulation bar (which it isn't supposed to do)

the problems occur when using formulas that depend on changing cells e.g.

A1 = "=B1+B2"
B1 = 10
B2 = 20


the result in A1 is 30..... which is all well and good but because it changes the formulation bar - if i then change the value of b1 to 5 A1 remains at 30...!

Any suggestions

Phil

Posted by Scott on June 06, 2001 8:06 AM

Re: sorry i mustn't hav explained my self well enough...

A couple of things come to mind. First, is it possible that you are in manual Calculation? If so, then when you copy down, the value would be the same until you hit F9 to calculate. Another possibility would be if the cell was formatted as Hidden (on the protection tab) and the worksheet protected. If you tried to copy this down, I believe it would copy down as values. Not sure if either of these is the problem. but, It's fixed itself now..... (however i wouldn't mind know y i did it) what was hapening was if i typed in =1+1 it wrote the value 2 in the cell (as it is supposed too) & it wrote 2 in the formulation bar (which it isn't supposed to do) the problems occur when using formulas that depend on changing cells e.g. A1 = "=B1+B2"