why does formula not work until I clear referenced cell ??
 

I'm putting formulas down an excel column but they don't work. Just by
chance I clicked on one of the referenced cells and pressed delete
(although there was nothing visible in it) and the formula then
worked.
e.g, I put formula in cell C2 which references cell B2 in
conjunction with other cells and nothing happens. I click B2 and press
delete and answer to formula now appears in cell C2.
Anyone know how I can deal with this? I have formulas to run down
nearly 300,000 rows and cannot do "press delete" for every occassion.
Thanks.
Colwyn.

why does formula not work until I clear referenced cell ??
 

You didn't post your formula but I suspect that someone has touched the
dreaded space bar.

=IF(TRIM(I3)="",1,2)
or
=IF(LEN(TRIM(I2))=0,1,2)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"colwyn" wrote in message
...
I'm putting formulas down an excel column but they don't work. Just by
chance I clicked on one of the referenced cells and pressed delete
(although there was nothing visible in it) and the formula then
worked.
e.g, I put formula in cell C2 which references cell B2 in
conjunction with other cells and nothing happens. I click B2 and press
delete and answer to formula now appears in cell C2.
Anyone know how I can deal with this? I have formulas to run down
nearly 300,000 rows and cannot do "press delete" for every occassion.
Thanks.
Colwyn.

why does formula not work until I clear referenced cell ??
 

As a guess your claculation mode is set to manual. Iit is under Tools |
Options | Calculation...

Press <Shift + Ctrl + Alt + F9
for a full recalculation of all cells.
--
HTH...

Jim Thomlinson


"colwyn" wrote:

I'm putting formulas down an excel column but they don't work. Just by
chance I clicked on one of the referenced cells and pressed delete
(although there was nothing visible in it) and the formula then
worked.
e.g, I put formula in cell C2 which references cell B2 in
conjunction with other cells and nothing happens. I click B2 and press
delete and answer to formula now appears in cell C2.
Anyone know how I can deal with this? I have formulas to run down
nearly 300,000 rows and cannot do "press delete" for every occassion.
Thanks.
Colwyn.

why does formula not work until I clear referenced cell ??
 

Don, thanks a lot. The second formula helped me get there. Saved a
whole lot of sweat!!!
Thanks again.
Colwyn.




On Oct 20, 4:30*pm, "Don Guillett" wrote:
You didn't post your formula but I suspect that someone has touched the
dreaded space bar.

=IF(TRIM(I3)="",1,2)
or
=IF(LEN(TRIM(I2))=0,1,2)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"colwyn" wrote in message

...

I'm putting formulas down an excel column but they don't work. Just by
chance I clicked on one of the referenced cells and pressed delete
(although there was nothing visible in it) and the formula then
worked.
e.g, *I *put formula in cell C2 which references cell B2 in
conjunction with other cells and nothing happens. I click B2 and press
delete and answer to formula now appears in cell C2.
Anyone know how I can deal with this? I have formulas to run down
nearly 300,000 rows and cannot do "press delete" for every occassion.
Thanks.
Colwyn.