Dysfunctional Formula Duplication Across Cells


J

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)))))
 
Ad

Advertisements

J

JohnH

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

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
 
Ad

Advertisements

J

JohnH

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top