Excel changes my formula when I add values!

[MJ13]

Hi, I was using excel97 but I've just changed to 2000 and I've go
problems using a sheet I've been using on 97 for months.

I have the formula: J10=SUM(B10:H10)-(I10+P10)
or: J10=SUM(B10:H10)-I10-P10

I've tried them both just to see if it made any difference.

What happens is that when I enter a value in I10 the J10 square flashe
black and excel changes the H10 in my formula to I10, so the formul
now reads: J10=SUM(B10:I10)-(I10+P10) which completely negates th
I10 value!

I have no idea what gives it the idea that it can change my formula
for me! I then have to paste the correct formula back in over the to
to correct it. Is there some box I've got checked which says "I thin
excel is cleverer than me and should change my formulas if it feel
like it"??

Cheers for any help, Marcus

 

[Frank Kabel]

Hi
never have seen such a thing before. The only thing I could imagine
would be a worksheet_change event macro. Do you use macros?

 

[hglamy]

Thank you, Frank !

Hi
never have seen such a thing before. The only thing I could imagine
would be a worksheet_change event macro. Do you use macros?

 

[MJ13]

To my knowledge I've never used macros. Everyone I ask has never seen o
heard of anything like this before.

I'm nearly at the point of rewriting the whole sheet. I'd stick excel9
back in but I don't have it anymore. The only other thing I've tried i
reformatting all the cells as currency but that didn't help.

Could I attach the sheet here, if that would help anyone?

cheers Marcus

 

[JulieD]

Hi Marcus

email it directly to me, if you like

Cheers
JulieD

 

[MJ13]

Hopefully I've attached a sample sheet here, can't get into th
newsgroup to access email addresses, just posting from excelforum.com

I inherited this sheet with my new job on an old computer with excel97
Having moved to a new computer with excel2000, I'm having problems wit
it. I have put this file on a floppy and tried it again on the ol
computer and it works fine.

The problem is in the 'over/under' column: the top 3 cells appear to b
OK, but when I input values into the bottom 4 cells the correspondin
'total sales' cell flashes black and the formula gets changed.

I don't know a lot about excel I must admit, I can't even figure ou
how to change things so I can add or edit text and labels so if someon
could help with that too it would be great.

Cheers, Marcus

Attachment filename: kbcweek07.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=58417

 

[JulieD]

Hi Marcus

umm it doesn't do it for me - entering figures into cells I10 through I16
doesn't affect the formulas in J - have you tried the following
1) doing it on someone else's computer
2) downloading the copy from the web & trying that one
3) before opening it in excel setting your security settings to high ...
(tools / macro / security) - there aren't any macros in this workbook but
there might be ones in your personal macro workbook interfering with this
workbook - this will stop any code from running and will let you know if
this could be the problem.
4) running a virus scan on your computer

other than that i'm at a bit of a loss as to explain the behaviour.

the reason that you can't change any of the labels is that the designer used
data / validation to protect cells (novel approach - love the input
messages) ... to change cells, select the ones that you want to change,
choose data / validation from the menu and press the clear all button
(you'll lose your cute messages) OR change the settings to "any value" and
press the OK button

let us know how you go.

Cheers
JulieD

 

[MJ13]

Off we go...

1) doing it on someone else's computer

It only happens on computers with excel from office2000pro (2 of them)
3 with different versions r OK.

2) downloading the copy from the web & trying that one

Still does it.

3) before opening it in excel setting your security settings to hig
...
(tools / macro / security) - there aren't any macros in this workboo
but
there might be ones in your personal macro workbook interfering wit
this
workbook - this will stop any code from running and will let you kno
if
this could be the problem.

Clean install in the last few weeks, but I tried anyway... didn't hel


4) running a virus scan on your computer

I scan every week, alternating between Norton (installed) and Tren
housecall (on the web). It would be a bizarre virus if it was!!

Any known bugs in 2000Pro? I think I'll just rewrite the sheet (tim
for some different funny messages anyway!) and see if it still doe
it.

Big thanks for trying to help

Cheers, Marcus