Cell reference - displays formula rather then referenced c..

Hi.

Doesn't look good. I found a posting entitled "Excel considers my formulas
as plain text" under the topic "Excel Workbook Functions" posted June '06 and
that string ended w/o a solution.
--
MikeA


"pH" wrote:

> Hello Mike,
>
> Unfortunately I can't help you, bust just wanted to tell that I'm fighting
> with the same problem. After making whatever modification to the formulas
> they are to be seen as text in the cell. There are 8 sheets in the workbook
> and the same bug occurs in every sheet.
> Hope anyone could help us!
>
> "MikeA" wrote:
>
> > I have Excel 2000, SP3. The spreadsheet has been working fine, but now has a
> > problem. Multiple users/updaters. We use the formula function to reference
> > and display the contents of a cell on another worksheet in the same workbook.
> > When you 1st set up the formula, it works fine. However, if you try to make
> > any modification to the formula in the cell (select an absolute value ($),
> > reference a different cell) it will no longer display the referenced cell. It
> > displays the formula instead. Clearing the cell or deleting the formula does
> > not help. That cell will no longer display the contents of another cell.
> > However, if I copy another cell that has never been used over that cell it
> > will again work, but only once. Once modified, it stops working.
> >
> > Any Ideas??
> >
> > thanks
> >
> > Mike
> > --
> > MikeA

We ran across this issue in Excel 2003 were a cell (A4) contains a reference (IE: "=Tab1!E3") and displayed the formula instead of the value from the referring cell. What I discovered was that cell "Tab1!E3" was formatted as TEXT. I had already changed the target cell (A4) from TEXT to GENERAL, but the TEXT formatting keeps passing through and changing A4's formatting. If I formatted Tab1!E3 and A4 as GENERAL, I never had the problem. But as soon as A4 references any cell that is formatted as TEXT, A4 takes on the TEXT formatting and it stays that way.

But that's not all. Once both cells were changed to GENERAL, I had to also modify the formula once for it to change. I simply retyped any character of "=Tab1!E3" and the value popped in.

I think there are two bugs here. One, formatting should not pass through a cell reference. Yes, if the target is set to GENERAL, then it should transparently take on the formatting of the source cell, not permanently change it. And Two, changes should be instant. I shouldn't need to change the formula (event to itself) to activate an update.

I hope that helps someone.