DGet function in Excel - Variable criteria
Hi everyone,
I have troubles using the Dget function in an Excel calculation sheet. The selection criteria for the unique record selection of a database depend on the result of a certain number of calculations. As a result, these criteria are variable.
I wrote the following:
DGET("Database";"Field Name";F1:H2)
where "F1:H2" corresponds to the range where the criteria are defined.
F G H
1 Field Name 1 Field Name 2 Field Name 3
2 =Variable 1 =Adress $A$24 =Variable 2
As long as I use cell references or variable names, the only DGET-result I get is #VALUE!.
The only way to get correct results is when I type the criteria corresponding to the actual case myself, as if it was a constant.
I also used Spinnaker-functions, which allow the criteria tp be typed inside the function itself instead of using a crieria table, but there I encountered the same problems.
As I suppose this problem should be solved in a very simple way, I'll only accord 50 points.
Greetings from Calacuccia
I have troubles using the Dget function in an Excel calculation sheet. The selection criteria for the unique record selection of a database depend on the result of a certain number of calculations. As a result, these criteria are variable.
I wrote the following:
DGET("Database";"Field Name";F1:H2)
where "F1:H2" corresponds to the range where the criteria are defined.
F G H
1 Field Name 1 Field Name 2 Field Name 3
2 =Variable 1 =Adress $A$24 =Variable 2
As long as I use cell references or variable names, the only DGET-result I get is #VALUE!.
The only way to get correct results is when I type the criteria corresponding to the actual case myself, as if it was a constant.
I also used Spinnaker-functions, which allow the criteria tp be typed inside the function itself instead of using a crieria table, but there I encountered the same problems.
As I suppose this problem should be solved in a very simple way, I'll only accord 50 points.
Greetings from Calacuccia
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ture,
From your example, it's clear you're the best paid man of your company (or was this just a virtual example), but anyway, I completely agree with you that there shouldn't be any problems at all, so I created a copy of my database in a new file and typed exactly identical formula's as I used before.
The weird thing is that in the new file, everything works perfectly (which is normal), but in the old file it remains impossible to enter variable criteria.
The only explanation I can think of is that my Excel-file is an converted Lotus 1-2-3 version 5 file, in which I had to correct a big number of unconvertible formula's myself. Apparantly, the data conversion is not 100% conform to one would expect.
I think there was a conflict between the data format in the converted Lotus-file and the by DGET used data formats, although I have not found any signs of different data formats.
If I know where this problem comes from, I can take caution when doing identical conversions in the future. This time, the application was not so huge, but in the near future, I expect the conversion of a enormous file.
Cri,
I haven't tried your solution, because I was pretty confident that it was not necessary to go that far. Anyway, I tried to find help on the "Evaluate"-VB function and could not find it in the index. Could you give a brief explanation of the function.
I'm sorry I did not mention that the application was a Lotus-conversion, but didn't think that this could have caused the kind of troubles I encountered.
Greetings,
Calacuccia
From your example, it's clear you're the best paid man of your company (or was this just a virtual example), but anyway, I completely agree with you that there shouldn't be any problems at all, so I created a copy of my database in a new file and typed exactly identical formula's as I used before.
The weird thing is that in the new file, everything works perfectly (which is normal), but in the old file it remains impossible to enter variable criteria.
The only explanation I can think of is that my Excel-file is an converted Lotus 1-2-3 version 5 file, in which I had to correct a big number of unconvertible formula's myself. Apparantly, the data conversion is not 100% conform to one would expect.
I think there was a conflict between the data format in the converted Lotus-file and the by DGET used data formats, although I have not found any signs of different data formats.
If I know where this problem comes from, I can take caution when doing identical conversions in the future. This time, the application was not so huge, but in the near future, I expect the conversion of a enormous file.
Cri,
I haven't tried your solution, because I was pretty confident that it was not necessary to go that far. Anyway, I tried to find help on the "Evaluate"-VB function and could not find it in the index. Could you give a brief explanation of the function.
I'm sorry I did not mention that the application was a Lotus-conversion, but didn't think that this could have caused the kind of troubles I encountered.
Greetings,
Calacuccia
xxxx
Add Module to your VBAproject (Alt-F11) of your workbook and add code below (to create user-defined function for on-sheet usage):
Public Function DoEval(a As String)
DoEval = ActiveSheet.Evaluate(a)
End Function
Now at your sheet you can place formula like that, where create desirable string with CONCATENATE function:
"=DoEval(CONCATENATE("STOC
(assume stock name is in A1 cell). Now if you change name in A1 cell, you'll get what you want
xxx
Remark: If you have only a few DGET functions: Write the _whole_ DGET formula with a sub directly into cell. i.e. pass the criteria as parameters to the a.m. sub.