Formula syntax error - chinese and gibberish

[Joshua Fandango]

Hi guys,

Firstly my thanks go out to those who sort out the problems of us minions. I've
learnt so much from these posts that I never thought possible - keep up the
good work guys...

I have a pretty good knowledge of Excel - years of experience with different
releases, but have never come across this situation before.
I have a a John Walkenbach termed 'megaformula' that contains ISERR, IF,
SEARCH, AND, OR, ISNA, VLOOKUP, LEFT & MID functions. Having got it to work I
opted to use copy and paste to automate it's use in a report, rather than mess
around with converting to the RC reference required by VBA.

Somehow the formula appears corrupted as the initial IF and some of the end of
the formula has vanished since running the procedure. It also contains a
couple of chinese (I can only assume) characters, which change to other chinese
characters when the cell it's contained in is activated, and some other symbols
which make the formula look like nonsense.

It doesn't appear that this is a problem as the formula still works correctly
when the procedure is run - which seems quite bizzare to me!

I'm running Excel 2000 (SP3) from Office 2000 on Windows NT 4. Using a Pentium
2 class PC with 96 meg of memory on a LAN.
Aside from telling my manager I need an updated computer does anyone have any
comments on just what is going on here?

Cheers,
JF.

 

[Dave Peterson]

You may want to post the formula (and cell address that contains it) to see if
anyone else can replicate your problem.

 

[Joshua Fandango]

Apologies for the delay - extended weekend cos of some bloke a while
ago.....

Here follows my fractured formula:

=ISERR(IF(SEARCH("missing",H2,1)>0,"Ian Strachan to provide detail to
APAK"))‡OR(H2="Over Group Credit Limit ",H2="Over Group Credit
Limit")D"Capital to Resubmit" LEFT(H2,7)="Invalid" "Dealer needs to be
set up on APAK system" ISNA(IF(VLOOKUP(MID(A2,2,4),'Z:\TRADE
FINANCE\Shared Area\Within Trade Finance\SUSPENCE ACCOUNT\Suspence
Queries\UPDATE LOOKUP
TABLES.xls'!UTILISATION_LOOKUP,2,0)="STOP","Dealer on stop per
utilisation report",VLOOKUP(C2,'Z:\TRADE FINANCE\Shared Area\Within
Trade Finance\SUSPENCE ACCOUNT\Suspence Queries\[UPDATE LOOKUP
TABLES.xls]INVOICES'!$C$1:$I$2000,7,0)))VLOOKUP(C2,'Z:\TRADE
FINANCE\Shared Area\Within Trade Finance\SUSPENCE ACCOUNT\Suspence
Queries\[UPDATE LOOKUP
TABLES.xls]INVOICES'!$C$1:$I$2000,7,0)VLOOKUP(MID(A2,2,4),'Z:\TRADE
FINANCE\Shared Area\Within Trade Finance\SUSPENCE ACCOUNT\Suspence
Queries\UPDATE LOOKUP TABLES.xls'!UTILISATION_LOOKUP,2,0)="STOP"
"Dealer on stop per utilisation report" C2 Z:\TRADE FINANCE\Shared
Area\Within Trade Finance\SUSPENCE ACCOUNT\Suspence Queries\[UPDATE
LOOKUP TABLES.xls]

I have no idea how this can still work. Sadly the aforementioned
chinese symbols don't copy, but there is a spurious 'D' and a few ''s
where they (sometimes) appear.

The formula resides in Sheet2 Cell I2 of my update workbook and is
pasted to cell I2 in my report.

It may be worth pointing out here that I'm not after a solution to
make the formula complete again - although I have actually forgotten
what the rest of it was, and didn't save it (silly boy) - rather I'm
just curious to what may have caused this situation to arise.

Still confused,
JF.

 

[Dave Peterson]

I've seen the box characters show up. For me, they're usually cause by an
alt-enter in the formula--to make the formula split lines at nicer spots. It
make reviewing the formula a little easier.

I don't have a guess why you got this problem, but next time it happens, you can
put an apostrophe in front of the formula ('=iserr(...)) to make it text.

Then you could use Chip Pearson's CellView addin that will show you want each
character is:
http://www.cpearson.com/excel/CellView.htm

Apologies for the delay - extended weekend cos of some bloke a while
ago.....

Here follows my fractured formula:

=ISERR(IF(SEARCH("missing",H2,1)>0,"Ian Strachan to provide detail to
APAK"))‡OR(H2="Over Group Credit Limit ",H2="Over Group Credit
Limit")D"Capital to Resubmit" LEFT(H2,7)="Invalid" "Dealer needs to be
set up on APAK system" ISNA(IF(VLOOKUP(MID(A2,2,4),'Z:\TRADE
FINANCE\Shared Area\Within Trade Finance\SUSPENCE ACCOUNT\Suspence
Queries\UPDATE LOOKUP
TABLES.xls'!UTILISATION_LOOKUP,2,0)="STOP","Dealer on stop per
utilisation report",VLOOKUP(C2,'Z:\TRADE FINANCE\Shared Area\Within
Trade Finance\SUSPENCE ACCOUNT\Suspence Queries\[UPDATE LOOKUP
TABLES.xls]INVOICES'!$C$1:$I$2000,7,0)))VLOOKUP(C2,'Z:\TRADE
FINANCE\Shared Area\Within Trade Finance\SUSPENCE ACCOUNT\Suspence
Queries\[UPDATE LOOKUP
TABLES.xls]INVOICES'!$C$1:$I$2000,7,0)VLOOKUP(MID(A2,2,4),'Z:\TRADE
FINANCE\Shared Area\Within Trade Finance\SUSPENCE ACCOUNT\Suspence
Queries\UPDATE LOOKUP TABLES.xls'!UTILISATION_LOOKUP,2,0)="STOP"
"Dealer on stop per utilisation report" C2 Z:\TRADE FINANCE\Shared
Area\Within Trade Finance\SUSPENCE ACCOUNT\Suspence Queries\[UPDATE
LOOKUP TABLES.xls]

I have no idea how this can still work. Sadly the aforementioned
chinese symbols don't copy, but there is a spurious 'D' and a few ''s
where they (sometimes) appear.

The formula resides in Sheet2 Cell I2 of my update workbook and is
pasted to cell I2 in my report.

It may be worth pointing out here that I'm not after a solution to
make the formula complete again - although I have actually forgotten
what the rest of it was, and didn't save it (silly boy) - rather I'm
just curious to what may have caused this situation to arise.

Still confused,
JF.

You may want to post the formula (and cell address that contains it) to see if
anyone else can replicate your problem.