Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    2 Star Lounger
    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!
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    2 Star Lounger
    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?

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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)
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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
    Attached Files Attached Files

  8. #8
    2 Star Lounger
    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 criteria-tables, giving them new names and introducing the new dget-functions 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 Excel-Sheet. You are right, I do not need the header-row 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 DGET-function 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!
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •