translating lotus 123 formulas into excel

[Seth]

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\sltr_mon.123>>$FI_CATTLE,2)); 2301.3

@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

@IF($B$2=7,"",(@VLOOKUP($A$2,<<h:\shared\sdlb\cattle\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.

 

[Harlan Grove]

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.