Results 1 to 8 of 8

20080128, 16:40 #1
 Join Date
 Mar 2002
 Location
 Germany, Germany
 Posts
 169
 Thanks
 0
 Thanked 0 Times in 0 Posts
More powerful DGET function? (XP SP3)
Hello everybody,
I am looking for a function which is more powerful than the DGET function built into excel. I have a table with approx. 40000 rows and five columns. Four of these columns contain parameter variations and the fifth contains a value, which could be thought of as the result of a function of the other four. Now I need to find a single row within all of them. This could be easily managed with the DGET function. My problem is: I need to identify 27 rows, all with different criteria. As far as I understood, the last parameter (the search criteria) has to be a range from the worksheet, which consists of only one area. This would mean that I would have to define 27 ranges with both the header and the individual search criteria, one for each row I have to find. For me, this does not seem to be very efficient but very hard to maintain.
Is there an easier way to realize something like this? Could I explain myself?
If someone has an idea about this, I would be happy, if he/she could share it with me.

20080128, 16:56 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: More powerful DGET function? (XP SP3)
These are typically done with some kind of array formula, whether they are exlicilty done or implicitly with a SUMPRODUCT.
Could you attach a dummy workbook, this would help us to see your setup and create the formula...
Steve

20080128, 17:19 #3
 Join Date
 Mar 2002
 Location
 Germany, Germany
 Posts
 169
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: More powerful DGET function? (XP SP3)
Hello Steve,
no problem, you will find it attached. Unfortunately, it won't work properly, since I had to strip down it dramatically... But I think you will get an idea. Thanks for your help in advance!

20080128, 18:07 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: More powerful DGET function? (XP SP3)
The easiset way, since you appear to want a table is to create a pivot table.
Select the rngDatabase,
Data  pivot table and pivot chart report...
Drag the 2 Calpha items to the column
Drag the 2 Cbeta items to the row
drag the NQD to the Data (it should be SUM)
Then enter [Finish]
After the table is created you can right click the "Total rows" and hide them all. Then click on each of the headings and uncheck the items you do not want to be included. this should match your table...
Steve

20080128, 22:16 #5
 Join Date
 Mar 2002
 Location
 Germany, Germany
 Posts
 169
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: More powerful DGET function? (XP SP3)
Thanks Steve. I do not want to work with PivotTables. I already tried them. I experienced two problems. One has to do with performance. The table posted here is just a short extract of what really is behind (there is a database behind it with millions of data rows). The other is about how you can modify the "look and feel" of the charts I want to create on basis of the extracted data. Somehow I would like to make it work as intended in the posted workbook (which also contains some nice features regarding cascading lists using names in the table that contain formulas instead of simple references  I think it's worth taking a look at).
Do you (or someone else) have an idea other than pivot table?

20080129, 12:49 #6
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,329
 Thanks
 3
 Thanked 218 Times in 201 Posts
Re: More powerful DGET function? (XP SP3)
You can use formulas in the criteria, so for example you can put Criteria1 in a cell and in the cell below it put:
=AND(DRIVE!B7=valFHALFA_AN,DRIVE!C7=valCALFA_AB,DR IVE!D7=valCALFA_AN,DRIVE!E7=valCBETA_AB,DRIVE!F7=v alCBETA_AN)
and then use these two cells as the criteria range for a DGET formula. You can adjust the formula to look at the relevant sheet and add any adjustments from your base values to the formula. It may be a little easier to use than the tables you have (though I think the tables are nice and clear, personally)
HTHRegards,
Rory
Microsoft MVP  Excel

20080129, 14:45 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: More powerful DGET function? (XP SP3)
How about the setup I have underneath your table? It gets the same values. I used the sumproduct to generate it. I created intermediate values based on your "selections" (+/2 like in your example). One difference is that I get "0" when there are not values to "get" where the DGET returns an error.
I also create a new named range (rngData) similar to your rngDatabase formula, only mine does not include the header row as I don't need it for the calculations...
Steve

20080130, 11:01 #8
 Join Date
 Mar 2002
 Location
 Germany, Germany
 Posts
 169
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: More powerful DGET function? (XP SP3)
Hello Rory and Steve,
thanks very much for your suggestions. Rory, I also think that the tables give a good overview of the criteria. But I also feel like it is a difficult solution regarding maintenance. The problem is that the number of rows and /or columns may change in the future, forcing me to introduce more of these criteriatables, giving them new names and introducing the new dgetfunctions to each cell separately. It seems it is not the best way to go. But I will try out your suggestion (I did not manage yet).
Steve thanks for reworking my ExcelSheet. You are right, I do not need the headerrow in the rngDatabase formula  at least if I follow your path. For my initial solution, it is essential to have the header. If it wouldn't exist, the DGETfunction wouldn't work, since it wouldn't know in which column to look up the criteria values. I have introduced four more named ranges (valActCALFA_AB, etc), they are linked to the header rows and columns. Using these names in your formula makes the formula more readable. With your solution it is very easy to extend the table, and so I like it very much. Thanks for your work!
I attached the reworked table again, just in case it is of gereral interest.
Thanks again to both of you for sharing your ideas!