Dysfunctional Formula Duplication Across Cells

[JohnH]

Using formula below to strip alpha & numeric characters from right side of
character string in ColB. When copying formula to underlying cells, row
reference changes as expected but formula result does not reflect the new row
until I either double click to reset the formula, or click of insert function
button and hit return. This formula worked one week ago. No known changes
to Excel. Other users report similar strange behavior. Tried "detect and
repair" to fix but no effect. Please help if you have any insight to this.
=IF(ISERROR(1*((IF(ISERROR((1*(LEFT(((RIGHT((TRIM($B2)),4))),1)))),0,(1*(LEFT(((RIGHT((TRIM($B2)),4))),1)))))&(1*(RIGHT(((RIGHT((TRIM($B2)),4))),3))))),"N/A",1*((IF(ISERROR((1*(LEFT(((RIGHT((TRIM($B2)),4))),1)))),0,(1*(LEFT(((RIGHT((TRIM($B2)),4))),1)))))&(1*(RIGHT(((RIGHT((TRIM($B2)),4))),3)))))

 

[JohnH]

Found problem myself: Excel pull-down menu; Tools, Options, Calculation tab,
check Automatic. Was on Manual - mystery why.

 

[Gord Dibben]

Common problem......not yet fixed in 2007 version.

Tools>Options>Calculation can be Auto or Manual.

Excel takes the Calculation mode each session from the settings on the first
workbook opened in that session.

i.e. If you saved Book1 with calc mode in manual and opened it first, calc
mode would be in Manual.

If you saved Book2 with calc mode in auto and opened it after Book1, Book2
would be in manual mode(Excel ignores the auto calc mode in this case).

If you close Book1 before opening Book2, Book2 will be in auto calc mode.

Confusing enough? <g>


Gord Dibben MS Excel MVP

 

[JohnH]

....and weird and a little bit funny. Am surprised I haven't run into this
before. Thanks for the help. JH