Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
translating lotus 123 formulas into excel
Hi everyone,
I'm converting some pretty extensive louts 1-2-3 files into excel and i'm running into some problems with formulas that access different workbooks. These are the formulas that are causing me problems: (@VLOOKUP($A$2,<<h:\shared\sdlb\cattle\slaughter\s ltr_mon.123$FI_CATTLE,2)); 2301.3 @IF(@VLOOKUP($A$2,<<h:\shared\sdlb\cattle\slaughte r\sltr_mon.123$FI_CATTLE,6)0,(@VLOOKUP($A$2,<<h :\shared\sdlb\cattle\slaughter\sltr_mon.123$FI_C ATTLE,6)),B$59); 3112.975 @IF($B$2=7,"",(@VLOOKUP($A$2,<<h:\shared\sdlb\catt le\slaughter\sltr_mon.123$FI_CATTLE,7))); Can anyone tell me what the correct formatting is for this to work in Excel? I also need to convert the referenced workbook to excel also, but that is going to be a bit more complicated as it also uses brio queries which are imbedded. Can I even link to a louts 1-2-3 file or do I need to convert it also before making the current one work? Thank you very much. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
translating lotus 123 formulas into excel
Seth wrote...
.... These are the formulas that are causing me problems: (@VLOOKUP($A$2, <<h:\shared\sdlb\cattle\slaughter\sltr_mon.123$ FI_CATTLE,2)); 2301.3 The bit from the semicolon on is an inline comment. Excel doesn't provide a direct equivalent. If you need to retain such comments, you're going to need to put them into cell comments in Excel. As for @VLOOKUP call, it should be =VLOOKUP($A$2, 'h:\shared\sdlb\cattle\slaughter\sltr_mon.XLS'!FI_ CATTLE,3) Note that I changed the .123 extension to .XLS. Also note that 123 uses zero-based indexing while Excel uses one-based indexing. @IF(@VLOOKUP($A$2, <<h:\shared\sdlb\cattle\slaughter\sltr_mon.123$ FI_CATTLE,6)0, (@VLOOKUP($A$2, <<h:\shared\sdlb\cattle\slaughter\sltr_mon.123$ FI_CATTLE,6)),B$59); 3112.975 Follow the example above. Again, handling the inline comment isn't part of translating the formula. =IF(VLOOKUP($A$2, 'h:\shared\sdlb\cattle\slaughter\sltr_mon.XLS'!FI_ CATTLE,7)0, VLOOKUP($A$2, 'h:\shared\sdlb\cattle\slaughter\sltr_mon.XLS'!FI_ CATTLE,7),B$59) @IF($B$2=7,"",(@VLOOKUP($A$2, <<h:\shared\sdlb\cattle\slaughter\sltr_mon.123$ FI_CATTLE,7))); Ditto. =IF($B$2=7,"",VLOOKUP($A$2, 'h:\shared\sdlb\cattle\slaughter\sltr_mon.XLS'!FI_ CATTLE,8)) I also need to convert the referenced workbook to excel also, but that is going to be a bit more complicated as it also uses brio queries which are imbedded. You may or may not be able to replace these brio queries with similar queries in Excel. I don't know because I have no idea what brio queries are. If they're database queries against an ODBC data source, you should be able to make equivalent queries in Excel. Can I even link to a louts 1-2-3 file or do I need to convert it also before making the current one work? You can't link to .123 files at all. Excel never has and never will read .123 files. And using .WK4 files may not be a good idea in the long run since Microsoft is in the process of deprecating Lotus .WK? file formats in Excel 2007. That is, Excel 2007 provides much less support for .WK? files than earlier versions of Excel. By the version after this, it's not impossible to believe there'll be no support whatsoever for .WK? files. If you still have 123 (I suspect you do since you'd be insane to attempt such a conversion exercise without having both 123 and Excel available), safer to save your .123 and .WK? files to .XLS files using 123. In my own experience, Lotus does a better job converting its own file formats to Excel than Microsoft does. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas don't work after copy/paste from Lotus 1-2-3 97 (Excel 20 | Excel Discussion (Misc queries) | |||
Translating formulas | Excel Discussion (Misc queries) | |||
Translating an IF formula from Lotus 1-2-3 to Excel | Excel Worksheet Functions | |||
text and numbers same cell and formulas still work (like lotus) | Excel Worksheet Functions | |||
Translating function names | Excel Discussion (Misc queries) |