Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula syntax error - chinese and gibberish
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. |
#2
|
|||
|
|||
You may want to post the formula (and cell address that contains it) to see if
anyone else can replicate your problem. Joshua Fandango wrote: 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 |
#3
|
|||
|
|||
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 wrote in message ... You may want to post the formula (and cell address that contains it) to see if anyone else can replicate your problem. Joshua Fandango wrote: 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. |
#4
|
|||
|
|||
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 Joshua Fandango wrote: 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 wrote in message ... You may want to post the formula (and cell address that contains it) to see if anyone else can replicate your problem. Joshua Fandango wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|