DGET FUNCTION??? 1

[RookieDBO]

Please help with DGET function. I cannot get it to work.
It works well with LOTUS123. I got the following formula from Lotus.

@DGET(<<U:\REF\Dep-desc.123>>$ZZ,&quot;DMM_Code&quot;,DEP=C4)
@DGET(<<U:\REF\Dep-desc.123>>$ZZ,&quot;DMM&quot;,DEP=C4)

How do I translate this into Excel?

Would someone send me a simple example at
Joe_delacruz@winners.ca.

I do not have access to the net at work.

Thanks,
RookieDBO

 

[DaleWatson123321]

Hi RookieDBO,

As a longtime Lotus 123 user, I can assure you that Excel is VERY INFERIOR in TWO key areas:

1) Lotus 123's former (Release 5.0) programming language as compared to Visual Basic, and

2) Database Formulas and Database Functionality.

Unfortunately, you are in for a SUPREME shock regarding the general difficulty in using Excel's database formulas and functions.

One example is the one you presented - i.e. where it appears that Excel is NOT capable of using the DGET function on a &quot;disk-based&quot; file - i.e. one which has NOT been loaded into memory. As you know, this IS possible with Lotus 123.

In Excel, my understanding is that the &quot;best&quot; you can achieve is to create a &quot;Workspace&quot;. In creating (saving a file as) a &quot;workspace&quot; file, Excel automatically saves all open workbooks as part of the &quot;workspace&quot; file.

Then, when you open your workspace file, all the workbooks are opened - which is what is REQUIRED in order for your DGET function to &quot;GET&quot; data from another workbook.

Bottom line is... with Lotus 123, its DGET function can pull data from a &quot;disk-based&quot; file WITHOUT having to &quot;open&quot; it. However, in Excel, the workbook HAS to be opened. This would of course apply to all the other database functions.

The other part of your example which might not be possible, is your reference to the &quot;criteria&quot; - i.e. where you refer to the criteria &quot;within&quot; the formula itself, as opposed to referring to a range of cells within the worksheet.

In the following formula, &quot;CRIT&quot; is a Range Name which refers to two cells in the worksheet: the top cell having the field name &quot;DEP&quot; and the cell below having the number the criteria &quot;gets&quot;.

=DGET(DGET_testdb.xls!data,&quot;DMM_Code&quot;,CRIT)

I tested the above formula, and it works, but (as mentioned) only with the &quot;testdb.xls&quot; file &quot;opened&quot;.

I hope this helps. If you have any further questions, don't hesitate to ask.

Also, other Excel experts hopefully can &quot;shed more light&quot; on this topic of DATABASE FORMULAS. I would &quot;love&quot; to be proven &quot;wrong&quot; about Excel's &quot;inferiority&quot;.

Regards, ...Dale Watson

HOME (for next 2 days): nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[Hasit]

I cannot add anything to this, except to say: RookieDBO, if anyone can help you with the DGET function, its DaleWatson123321.

 

[RookieDBO]

Thanks for the information.

RookieDBO

 

[JVFriederick]

I agree with Dale. I especially miss the clean, fast, and easy query capability of external databases via ODBC connections within 123 V5.

On the other hand, I would not want to give up the filter and matrix array features of XL either.

It has to be a conspiracy.

 

[DaleWatson123321]

Update...

First, THANKS to Hasit and JV for your support.

Here I go again... I first &quot;slammed&quot; Excel for being inferior to Lotus 123, and I still stick by that position. In my (humble) option, I maintain that Lotus 123’s extremely versatile database functions and “functionality” are FAR superior to the “filter” and “array” options in Excel.

However, as in the past, I've persisted in looking for a workaround &quot;equivalent&quot; function or solution, and I found one.

I discovered that the VLOOKUP function works well in RookieDBO's situation, and actually pulls in the data WITHOUT having to open the other file.

Here's the formula:
=VLOOKUP(C4,'C:\Test_db.xls'!data,2,FALSE) - for column A
=VLOOKUP(C4,'C:\Test_db.xls'!data,3,FALSE) - for column B

RookieDBO is importing data into a sheet starting at column C. He has the above formulas as &quot;resident formulas&quot; in columns A & B. These formulas then &quot;pull in&quot; the required information from the SEPARATE file (in this case called &quot;Test_db.xls&quot - which does NOT have to be opened.

If there is a preference to show a &quot;BLANK&quot; cell instead of &quot;#N/A&quot;, the following formula will provide that option.

=IF(ISNA(VLOOKUP(C4,'C:\Test_db.xls'!data,2,FALSE)),&quot;&quot;,VLOOKUP(C4,'C:\Test_db.xls'!data,2,FALSE))

If anyone would like a copy of the two files, don't hesitate to ask.

Regarding my complaints about the “inferiority” of Excel, allow me to provide just a couple of “small examples”…

1) In Lotus 123, one can create extremely complex criteria for the database functions – i.e. where the criteria can have as many COMPOUND conditions and references and one can imagine or require. In Excel, by comparison, this sort of thing is EXTREMELY limited, and DIFFICULT to set up.

2) In Lotus 123, one can actually use the same complex database functions as “text” as part of the programming code, but yet the code treats these powerful “text” formulas as if they are “live” formulas. One can therefore easily create “test conditions” like the following:

{IF @DSUM(data,”DOLLARS”,dol_crit)>=100}{subroutine_1}
{IF @DSUM(data,”DOLLARS”,dol_crit)<100}{subroutine_2}
{IF @DCOUNT(data,”EMPLOYEE”,emp_crit)>10}{other_subroutine}

If anyone out there can reveal any obscure or hidden functionality in Excel which comes close to the above, then I (and I expect other former Lotus 123 users and Excel users) will be GREATLY appreciative.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca