formula in a cell that won't turn over.

joeq

Board Regular
Joined
May 31, 2003
Messages
109
Hi all.

Got a weird one here.

What would cause a cell that contains a formula (which should translate to some value on another sheet), to only display the formula and not 'turn over' to show the value it should translate to.

I have other cells on the sheet that used to do this corectly, but now if I copy their existing formula, then clear out the cell, then past that formula back in, it shows only the formula.

thanks, joe
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Have you checked if the cell is formatted as Text?
 

joeq

Board Regular
Joined
May 31, 2003
Messages
109
Yes,

Its formatted as text. Im looking at a cell on the sheet right now that is also formatted as text and is working ok.

If I copy its formula, clear out the cell and recopy the formula back, itl just show the formula.

The only thing Ive done recently that might have caused this is Ive renamed the source tab. But Ive looked at it and it seems to have been accounted for with the new name everywhere.

thanks, Joe
 

joeq

Board Regular
Joined
May 31, 2003
Messages
109
Well....

All I can say is I must have stumbled onto the answer (seems to be my strength these days)

When coping a formula from a cell on sheet1 into a cell on sheets 2 - where I'd like the formula to result in the same value - the destination cell didn't seem to like it if the formula contained $A2.

But it was ok if the formula was $A$2 instead.

Don't know why ;)
 

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
$A$2 means the cells A2 in anchored, i.e. if you copy $A$2 and past at a position below to the right it will still refer to A2.

On the other hand if the formula is $A2 only the column is anchored, not the row, i.e. do the same copy and past and you'll find it still refers to column A, but 2 has changes to a number that equals to as many rows below the original copy you pasted.

Here's another example:
Book5
ABCD
1PricePrice tax incl.Tax
2$ 25.00$ 26.8757.50%
3$ 30.00$ 32.250
4$ 35.00$ 37.625
5$ 40.00$ 43.000
6$ 45.00$ 48.375
7$ 50.00$ 53.750
8
Sheet1


Copy this table in a fresh Excel sheet, but type only in the first formula. The use the fill handle and copy down the formula to the last row that has a price. Then double-click on the last cell and see to what cells the last formula refers to. It should be A7 and C2. C2 is anchored ($C$2) and does not move.

Make an example for $A2 and post back.

RAM
 

Watch MrExcel Video

Forum statistics

Threads
1,118,069
Messages
5,570,004
Members
412,304
Latest member
citrus
Top