Cell reference in formula always returns a zero value
 

Example:

Cell A1 contains the number 6.
Cell A2 contains the formula =A1 but displays a value of 0.

The calculation option is set to Automatic.

What has gone wrong? I am obvious missing something here.

Cell reference in formula always returns a zero value
 

I would guess that A1 contains text, rather than the number 6. Look in the
formula box when A1 is selected, and see whether you've got any leading or
trailing spaces or other characters.
--
David Biddulph

"Jim Alderman" wrote in message
ups.com...
Example:

Cell A1 contains the number 6.
Cell A2 contains the formula =A1 but displays a value of 0.

The calculation option is set to Automatic.

What has gone wrong? I am obvious missing something here.

Cell reference in formula always returns a zero value
 

No, A1 is numeric. I created the simple example for the ease of
discussion. I have existing spreadsheets with elaborate formulae that
used to work fine. Now they all fail. Cells that contain a formula
display the value of the formula before my issue began. If I go to one
of the referenced cells and change its value, the formula cell still
shows the original value. Go figure - I have no idea what is going
on here.


David Biddulph wrote:
I would guess that A1 contains text, rather than the number 6. Look in the
formula box when A1 is selected, and see whether you've got any leading or
trailing spaces or other characters.
--
David Biddulph

"Jim Alderman" wrote in message
ups.com...
Example:

Cell A1 contains the number 6.
Cell A2 contains the formula =A1 but displays a value of 0.

The calculation option is set to Automatic.

What has gone wrong? I am obvious missing something here.

Cell reference in formula always returns a zero value
 

Jim

=A1 will return whatever is in A1 whether it is text or numeric.

If it shows a zero something else is going on.

How does the 6 get entered to A1.....manually or by formula?

Do you have any helpful event code in the sheet that is changing the 6 in A2 to
a zero?

Right-click on sheet tab and "View Code"

Anything there that could be doing the deed?

Right-click on the Excel logo at left of File on the worksheet menubar and
select "View Code".

Anything there?


Gord Dibben MS Excel MVP

On 27 Nov 2006 16:58:35 -0800, "Jim Alderman" wrote:

Example:

Cell A1 contains the number 6.
Cell A2 contains the formula =A1 but displays a value of 0.

The calculation option is set to Automatic.

What has gone wrong? I am obvious missing something here.

Cell reference in formula always returns a zero value
 

Jim

ToolsOptionsCalculation.

Are you set to "Automatic"?


Gord

On 27 Nov 2006 17:21:20 -0800, "Jim Alderman" wrote:

No, A1 is numeric. I created the simple example for the ease of
discussion. I have existing spreadsheets with elaborate formulae that
used to work fine. Now they all fail. Cells that contain a formula
display the value of the formula before my issue began. If I go to one
of the referenced cells and change its value, the formula cell still
shows the original value. Go figure - I have no idea what is going
on here.


David Biddulph wrote:
I would guess that A1 contains text, rather than the number 6. Look in the
formula box when A1 is selected, and see whether you've got any leading or
trailing spaces or other characters.
--
David Biddulph

"Jim Alderman" wrote in message
ups.com...
Example:

Cell A1 contains the number 6.
Cell A2 contains the formula =A1 but displays a value of 0.

The calculation option is set to Automatic.

What has gone wrong? I am obvious missing something here.


Gord Dibben MS Excel MVP

Cell reference in formula always returns a zero value
 

Gord:

Thanks for your suggestions. However, nothing shows up as an issue in
either code view.

In my simple example, the 6 was keyed into A1.

It appears that something has failed within Excel. I can no longer
create a formula with a cell reference and have the value of that cell
returned to the formula. Also, existing formulae will no longer update
when the value of a referenced cell is changed.

It looks like I will need to reload Excel.



Gord Dibben wrote:
Jim

=A1 will return whatever is in A1 whether it is text or numeric.

If it shows a zero something else is going on.

How does the 6 get entered to A1.....manually or by formula?

Do you have any helpful event code in the sheet that is changing the 6 in A2 to
a zero?

Right-click on sheet tab and "View Code"

Anything there that could be doing the deed?

Right-click on the Excel logo at left of File on the worksheet menubar and
select "View Code".

Anything there?


Gord Dibben MS Excel MVP

On 27 Nov 2006 16:58:35 -0800, "Jim Alderman" wrote:

Example:

Cell A1 contains the number 6.
Cell A2 contains the formula =A1 but displays a value of 0.

The calculation option is set to Automatic.

What has gone wrong? I am obvious missing something here.

Cell reference in formula always returns a zero value
 

Gord:

Yes, I am set to automatic.


Gord Dibben wrote:
Jim

ToolsOptionsCalculation.

Are you set to "Automatic"?


Gord

On 27 Nov 2006 17:21:20 -0800, "Jim Alderman" wrote:

No, A1 is numeric. I created the simple example for the ease of
discussion. I have existing spreadsheets with elaborate formulae that
used to work fine. Now they all fail. Cells that contain a formula
display the value of the formula before my issue began. If I go to one
of the referenced cells and change its value, the formula cell still
shows the original value. Go figure - I have no idea what is going
on here.


David Biddulph wrote:
I would guess that A1 contains text, rather than the number 6. Look in the
formula box when A1 is selected, and see whether you've got any leading or
trailing spaces or other characters.
--
David Biddulph

"Jim Alderman" wrote in message
ups.com...
Example:

Cell A1 contains the number 6.
Cell A2 contains the formula =A1 but displays a value of 0.

The calculation option is set to Automatic.

What has gone wrong? I am obvious missing something here.


Gord Dibben MS Excel MVP

Cell reference in formula always returns a zero value
 

Gord:

After further testing I have found that the problem is simply that
formulae no longer work. A formula of =1+1 displays a value of 0. It
appears that my Excel installation is hosed.


Jim Alderman wrote:
Gord:

Yes, I am set to automatic.


Gord Dibben wrote:
Jim

ToolsOptionsCalculation.

Are you set to "Automatic"?


Gord

On 27 Nov 2006 17:21:20 -0800, "Jim Alderman" wrote:

No, A1 is numeric. I created the simple example for the ease of
discussion. I have existing spreadsheets with elaborate formulae that
used to work fine. Now they all fail. Cells that contain a formula
display the value of the formula before my issue began. If I go to one
of the referenced cells and change its value, the formula cell still
shows the original value. Go figure - I have no idea what is going
on here.


David Biddulph wrote:
I would guess that A1 contains text, rather than the number 6. Look in the
formula box when A1 is selected, and see whether you've got any leading or
trailing spaces or other characters.
--
David Biddulph

"Jim Alderman" wrote in message
ups.com...
Example:

Cell A1 contains the number 6.
Cell A2 contains the formula =A1 but displays a value of 0.

The calculation option is set to Automatic.

What has gone wrong? I am obvious missing something here.


Gord Dibben MS Excel MVP

Cell reference in formula always returns a zero value
 

Jim,
Is it possible the the cell A2 has a custom format "0" (0 must be in
quotes)?
rgds

André


"Jim Alderman" wrote in message
ups.com...
Example:

Cell A1 contains the number 6.
Cell A2 contains the formula =A1 but displays a value of 0.

The calculation option is set to Automatic.

What has gone wrong? I am obvious missing something here.

Cell reference in formula always returns a zero value
 

And

Thanks for the thought, but there is no custom format. All formulae
are failing, both in existing spreadsheets and new ones. When you open
a new spreadsheet and type =1+1 into A1 and it displays 0, Excel must
certainly have experienced some type of corruption.

Jim

Andre Croteau wrote:
Jim,
Is it possible the the cell A2 has a custom format "0" (0 must be in
quotes)?
rgds

André


"Jim Alderman" wrote in message
ups.com...
Example:

Cell A1 contains the number 6.
Cell A2 contains the formula =A1 but displays a value of 0.

The calculation option is set to Automatic.

What has gone wrong? I am obvious missing something here.