formula in a cell that won't turn over.

[joeq]

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

 

[Norie]

Have you checked if the cell is formatted as Text?

 

[joeq]

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

 

[Aladin Akyurek]

Activate Edit|Find.

Set Find what to:

=

Activate the Replace tab.

Set Replace with to:

=

Another option is to run ASAP Utilities (www.asap-utilities.com) on the formula cells.

 

[joeq]

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]

$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
	A	B	C	D
1	Price	Price tax incl.	Tax
2	$ 25.00	$ 26.875	7.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