Excel 2007 : Forcing a state change

[DvDj]

=IF(A1="",B2,A1)

The formula above has been working well elsewhere in my spreadsheet it basically says that if A1 is blank then use the info in cell B2 otherwise use A1. In my spreadsheet B2 looks up a value and i use A1 to override this value if i need to.

However i am trying to do the same thing with A1 using a drop down list, the formula works until you change A1's value using the drop down list, at which point nothing happens as Excel doesnt seem to notice this state change as it would when you physically type something in.

Does anybody know of a way around this or a way of forcing a state change??

Many thanks

Stuart

[NBVC]

Are you in Automatic Calculation Mode?

Go to the Office Button and select Excel Options. Select Formulas and then make sure Automatic is selected in the Calculation Options area

[DvDj]

No i'm not... at least not that i know of!!

[DvDj]

Sorry NBVC, just checked options and 'Automatic calculation' mode is checked. So does this mean i need to press F9 each time?

[NBVC]

if pressing F9 everytime works, then setting the Automatic should work too.

Maybe you need to post the sheet to see what's going on.

[DvDj]

Just tried again pressing F9 and that doesnt work either

[NBVC]

Post the sample workbook

[DvDj]

Hopefully the attached makes sense. The cells that i am refering to are E26, Z26 and AB26

Many thanks

[NBVC]

It's working for me in 2007 and also in 2003... don't know what is going on.

[DvDj]

Thank you for the time you spent looking into that for me NBVC.

Your right there is nothing wrong with the formula. It turns out that if I work in 'normal view' then it works fine. I had been working in 'Layout' view and in this view it doesn't work as the spreadsheet is speperated into two pages viewable side by side, which is how i wanted it. So only the page you are in refreshes as appossed to the whole document.

Your not aware of a way of having Excel refresh... say the whole workbook upon change or at least all pages in view?

Anyway many thanks again for your help

[NBVC]

As far as I know, as long as you have Automatic calculations on...it should work for the whole worksheet/workbook....

It worked for me in both normal view and in layout view.

Pressing F9 refreshes entire sheet too...so it should also work in any view.

[DvDj]

NBVC,

Many thanks for your help again... I havent got a clue what is going on as I have just tried it on a different computer and it all works fine!!!! just as you said.

So going to go through settings etc. many thanks.