Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

DGET FUNCTION

Hi,
I have table which contents the detailed recept of matl.
Now, I have another summery sheet in the same file. I wish to pull the
latest purchased rate of specific items. I have tried this by using DGET /
DMAX function.
but it gives results for only on single items.
for, your ref. i am providing herewith the summery sheet
im_code (DESIRED OUTPUT)
ATM0093 =DGET(GIN!A:BV,"IM_INVRT",$A$1:$A$7)
ATM0094 =DGET(GIN!A:BV,"IM_INVRT",$A$1:$A$7)
PTM0748 =DGET(GIN!A:BV,"IM_INVRT",$A$1:$A$7)
ATM0576 =DGET(GIN!A:BV,"IM_INVRT",$A$1:$A$7)
ATM0577 =DGET(GIN!A:BV,"IM_INVRT",$A$1:$A$7)
ATM0579 =DGET(GIN!A:BV,"IM_INVRT",$A$1:$A$7)
but the value returns #NUM!
Is there any way to give condition in this like
=DGET(GIN!A:BV,"IM_INVRT",$A$1,$A$5)
that means the crietria would be col. heading & the specific row no.

--
Thanks,
Vikram P. Dhemare


Post your answer or comment

comments powered by Disqus
I am a Lotus123 user and have switched to Excel. I cannot get exactly what i want from the DGET function in Excel.
The lotus formula is:
@dget($ProdMaster;"Description";CODE=A10)
I know that there is a DGET function in Excel, but i am confused at the 'criteria' requirement. I used the VLoookup function, but it doesnt give an exact match!
Much obliged if someone can help!

I need a formula that will search a worksheet "Source" for specific data
"account#"
and return data in the same row but in a different column "balance". The
data would populate a different worksheet (destination).

Example Source:
Account Balance
123 100
152 150
942 99

I need the formula to search through the account numbers for a specific
number and return the balance to the other sheet.
Notes:
The source data sheet has blank rows.
The source data is updated regularly.
New accounts may be added to the source data.

The offset function has worked but requires extra work as the source data
sheet must be open to make the destination fields fill in. Once the source
data sheet is closed or deleted the destination fields have error values.

I believe the DGET function may have the answer but can not figure out the
syntax.

I need a formula that will search a worksheet "Source" for specific data
"account#"
and return data in the same row but in a different column "balance". The
data would populate a different worksheet (destination).

Example Source:
Account Balance
123 100
152 150
942 99

I need the formula to search through the account numbers for a specific
number and return the balance to the other sheet.
Notes:
The source data sheet has blank rows.
The source data is updated regularly.
New accounts may be added to the source data.

The offset function has worked but requires extra work as the source data
sheet must be open to make the destination fields fill in. Once the source
data sheet is closed or deleted the destination fields have error values.

I believe the DGET function may have the answer but can not figure out the
syntax.

Hello,

I'm trying to use the function DGET and get data from another excel
worksheets and it doesn't seems to work.

Does this looks correct : DGET ('c:tmp[tmp_excel.xls]'!A1:D20;"ID";A1:A2)
?

thanks in advance !

Nader

I need to produce something similar to "search results". Example:

A1: BLUE BEAR RUNS
A2: GREEN TURTLE FLIES
A3: BLACK GROUNDHOG EATS
A4: RED MONKEY JUMPS
A5: GREEN GROUNDHOG RUNS

I am looking for something similar to "DGET" function, but will produce multiple results. Cell B1 would contain the entered "search value", example: "Green".

Cell B2 would result in "GREEN TURTLE FLIES"
Cell B3 would result in "GREEN GROUNDHOG RUNS"
(since they both contain the word green, they are both returned).

Thanks in advance. I can be reached at joshhazel"at"sbcglobal.net if need be.

Help, this is my first time on this, but it seems to help everyone else on here. I'm trying to use the DGET function and I've run into a snag. The info I am trying to pull includes grades, ie A+, A, B, C... and numerical values. Basically if one cell reads A+, and a few other criteria, then the cell is the numerical value. I get the right result with all the grades except for A. The error that comes up is #NUM!. Any help would be greatly appreciated.

Hi,

I have a column of values that I retrieve from a database based on
dates. It will always try to retrieve an entire week (Sun - Sat) at a
time. This is done for charting purposes. My problem is that if it's
only Wednesday, the chart will show 0's for thurs, fri, and sat b/c the
DGET function can't find the values for those days (because they haven't
been calculated yet). So, I either need to have the DGET function
return those days as null values, or have the chart not graph the cells
that have #VALUE! in them. Any ideas???

Thanks for the help,

Matt

--
matthoffman33
------------------------------------------------------------------------
matthoffman33's Profile: http://www.excelforum.com/member.php...o&userid=32148
View this thread: http://www.excelforum.com/showthread...hreadid=539134

I am trying to convert a Lotus file over to Excel, and am having some trouble
converting an error handling dget function.

=IF(ISERR(DGET(Databaseread,"Name","GROUP
ID"=GroupNumber)),VLOOKUP(GroupNumber,Databaseread ,4,FALSE),DGET(Databaseread,"NAME","GROUP ID"=GroupNumber))

This is the function that was used in Lotus; it returns the name of a
company by looking at the ID number. I need to keep it as pure as possible to
the Lotus file.
Any help would be appreciated- thanks!
--
earth21994

I have a dget function that I want to use different range and criteria ranges
depending on a flag. I thought that index would work but I am getting a
value error. Thanks in advance for any help.

Here's the formula:

=dget(index(rangearray,1),az11,index(rangearray,3))

whrere
Med1_602
Med2_602
Med1_602Crit
Med2_602

are in a range called rangearray. These are all ranges that I want to use
in the dget formula depending on other criteria.

If I do the index function independently it gives me the correct range but
in the dget formula I get an error?

Thanks, I posted this last week on the function area, but didn't receive a
solution, hoping someone here know one.

Thanks in advance for any help.

I am wanting to replace some VLookup formulas with the DGet function but cannot get the DGet to work. The syntax seems simple enough but all I get is #N/A errors. The criteria exist in the database range, are not duplicated and I am trying to retrieve the value from the second column of the database range as such:

=DGET(E6:F500,2,I2:I500)

A possible complexity is that the database range is actually part of a pivot table and that I cannot use headings for the columns, or is it simply that you cannot use DGET when your criteria is a long list rather than just a heading and one criteria value?

Any suggestions would be appreciated.

My data set is below. I want to use DGET function to link data from one
workbook to another without the risk of the data in the new workbook becoming
incorrect because I have added rows are sorted the main workbook to add or
change information. I originally used paste link and then realized when I
made changes to the main worksheet the links didn't update in the other
workbook. For instance I want to be able to do a DGET formula that would
pull Company A's data for each FY and put into another workbook. What would
my DGET formula look like? I understand I would have to a have a formula for
each cell (i.e. for each month and year). Thanks.

Company FY Jly. Aug. Sep.
A FY 2005 $6,502 $9,899 $28,916
A FY 2004 $6,949 $4,065 $5,286
A FY 2003
B FY 2005 $8,117 $2,195 $747
B FY 2004 $2,166 $3,816 $814
B FY 2003
C FY 2005 $283 $195 $1,716
C FY 2004 $404 $2,658 $982
C FY 2003

Hi,

I have a column of values that I retrieve from a database based on dates. It will always try to retrieve an entire week (Sun - Sat) at a time. This is done for charting purposes. My problem is that if it's only Wednesday, the chart will show 0's for thurs, fri, and sat b/c the DGET function can't find the values for those days (because they haven't been calculated yet). So, I either need to have the DGET function return those days as null values, or have the chart not graph the cells that have #VALUE! in them. Any ideas???

Thanks for the help,

Matt

Hi All...

I have a workbook file containing several sheets, is it possible to pull data from another sheet or workbook with multiple column criteria.

For example :

i want to filled a cell in Sheet B from Luas_Tanam column in sheet A, i'm thinking a database query like this :

--select from sheet "A" where column "estate" = "BSRE" or column "Divisi" = "Div 01" or column "No_Block" = "J17" or column "Tahun_Tanam" = "2008"--

It goes like =DGET() functions, but i can't get it work, maybe any other solutions??

i've attached example of the workbook, sory for the bad "ENGLISH" question, hope you know what i mean.

Regards,
Nabihan

Hi there,

I'm getting an error with a DGET function. One of the criteria fields
is a formula (an IF statement) which I assume is the problem.

Is there any way round this i.e. being able to change the criteria
based on the contents of another cell?

Many thanks - David

To anyone that has encountered this issue, (see attached file)
I am trying to use the database DGET function and I continue to get the error result #NUM! which according to Excel help indicates duplicate values in the database. I reviewed the database by using auto filter on the criteria I entered for the DGET function and only 1 record is returned. So, how can there be a duplicate? I have also had bad results using the DSUM function especially when the criteria has similar items in the field but not a redundant item. Any help would be much appreciated.

I am trying to convert a Lotus file over to Excel, and am having some trouble
converting an error handling dget function.

=IF(ISERR(DGET(Databaseread,"Name","GROUP
ID"=GroupNumber)),VLOOKUP(GroupNumber,Databaseread,4,FALSE),DGET(Databaseread,"NAME","GROUP ID"=GroupNumber))

This is the function that was used in Lotus; it returns the name of a
company by looking at the ID number. I need to keep it as pure as possible to
the Lotus file.
Any help would be appreciated- thanks!
--
earth21994

I want to use the DGET function to populate a results table in an excel spreadsheet. Two of the "criteria" I'm using for the DGET function will be stored in the first row and first column of the output table. Unfortunately, it looks like DGET requires a contiguous array to be used as the "criteria" input. I have tried using an array constant (i.e. a hardcoded array using curly braces {}), but am getting errors. There seem to be two separate problems occurring:
1. The DGET function returns an error when I try to use an array constant
2. I don't think array constants can contain cell references; they literally have to be constants.

Any input will be greatly appreciated. I've attached a spreadsheet showing an example of the problem.

Thanks!

Is it possible to create a validation list containing 2 NON-Consecutive rows?

i.e. Range A1:D1 AND Range A5:D5 only?

I'm trying to use this validation list or Named Range as a CRITERIA in a DGET Function and only want to reference the column headers and a specific row of non-consecutive data in the function.

As always, thanks for any insight and help in advance!

TNT235

Hello all, I hope someone can help me again. I really appreciate it, and I've tried to explain it in more detail this time.

With the following procedure, I am using a Part ID number to look up that part's location in a worksheet. To do this, I am using the DGET function which now works, thanks to all who helped with those issues.

But now I'm trying to harness the outcomes from that function.
--It displays #VALUE! if the part ID number was not found in my worksheet and therefore the function can't return a location.
--It displays #NUM! if the part ID number was found in multiple records/rows in my worksheet and so it has multiple corresponding locations.

I am trying to construct an IF section that will clarify what these mean to anyone running this macro. I want to replace #VALUE! with "Not Found" and #NUM! with "Numerous".

The issue arises when I compare the result of the function with the #VALUE! or #NUM! strings. I've tried StrComp and a simple equals sign (as below) I've even tried having Option Compare at the top, though I'm not familiar with how that would change things. But I am getting "Type Mismatch Error 13" all the time. I also get that error when set my DGET function = to a variable (Dim'ed as string)

I'm not seeing whats going on, hopefully I explained it alright.
Thanks in advance!

Variables to point out:
---Sheeti is a Publically defined Worksheet
---'CriteriaRange : Publically defined Range that is empty (until assigned criteria for my DGET function later within my loop)
---'InventoryRange : Pubically defined Range that contains inventory data that I look through with my DGET function (Items in the inventory are identifyable by a string I call "sItem" and each has a location in a corresponding column that I am looking for with my DGET function)


	VB:
	
 LoopThruParts() 
     
    Dim y As Integer 
    Dim sItem As String 
    Dim ValueError As String 
    Dim NumError As String 
     
    ValueError = "#VALUE!" 
    NumError = "#NUM!" 
     
    Do 
        sItem = Sheeti.Cells(y, 6).Value 
         
        If sItem = "" Then 
            Sheeti.Cells(y, 2).Value = "" 
        Else 
            CriteriaRange.Cells(2, 1).Value = "=""=" & sItem & """" 
            Sheeti.Cells(y, 2).Value = Application.DGet(InventoryRange, 3, CriteriaRange) 
             
             'The following line is where I get error 13
            If Sheeti.Cells(y, 2).Value = ValueError Then 
                Sheeti.Cells(y, 2).Value = "Not Found" 
            ElseIf Sheeti.Cells(y, 2).Value = NumError Then 
                Sheeti.Cells(y, 2).Value = "Numerous" 
            End If 
             
        End If 
         
        y = y + 1 
    Loop Until IsEmpty(Sheeti.Cells(y, 5)) = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am using a loop to redefine criteria for a DGET worksheet function for each part in a list.

However, in order to have DGET recognize criteria, the cell needs to appear:
=partnumber
But since = signs automatically try to interpret the following as a formula I need to enter into the cell:
="=partnumber"
(the partnumber is a variable thats redefined on each loop)

But VBA won't enter the equal signs/quotes into the cell before and after the part number as shown because the signs are being interpreted as my code.

The following code is the line that will not enter what I want into the cell within my defined "CriteriaRange" that I refer to in my DGET function.


	VB:
	
CriteriaRange.Cells(2, 1).Value = "=" & """ & " = " & sItem & """ 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This didn't work either because VBA interpreted it as a TRUE/FALSE test whether the = sign was = to the partnumber (always false)


	VB:
	
CriteriaRange.Cells(2, 1).Value = "="=" & sItem & """ 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Basically, I can't find a way to code VBA to enter quotes and equal signs into a cell along with a string defined by a variable because quotes and equal signs are themselves part of VBA code.

Hope theres a simple fix.
Thanks again

When using the dget function how do you list mulitple returns that match the criteria.

for example if 223 is the max and three names match 223 what would be the best way to list them in a spreadsheet

I have a database of information for the products I deal with (i.e. Product
names, Product ID numbers, Product Pricing...). Using the DGET function
formal, I can populate a list of products according to the criteria used in
DGET. I was wondering is there a formula or way to identify duplicate
entries within a field (i.e. - Product ID numbers). Some products use the
same accessories and I want to limit the list of accessories (or at least
identify the duplicates) so that I can isolate the accessory one time without
having it listed several times. All I need is help identifying duplicates
within the field/range and I can take it from there. Thank you all for any
help you can give me.

Hi all!

I am designing a configuration tool that should also creat a list of parts. I have created a font-end worksheet for the user to make selections and a "price list" sheet that updates as selections are made.

I need now another sheet that looks to the price list and shows only the items with a quantity of greater than 1 (worksheet labled "System Detail Report").

So far I can accomplish this with a vlookup fuction but that gives me MANY rows that do not contain data. Is there a way to update a cell with data from a range only if certain criteria are met (e.g., quantity is greater than 0)?

I've tried PivotTables and Dget function and I can't seem to find a way to do this. Do I need a VBA Script?

I am using Excel 97...the workbook is attached...Thanks in advance for your help!!!

What can I do to avoid a performance slowdown as a result of the following?
My 100 kb workbook has 3 worksheets, two of which each have 10 DGET functions
pulling from the other worksheet database. The worksheets have less than 200
rows each. Calculation and cursor/scroll motion is fine while I make data
entry changes, but once I print or use print preview, the workbook slows down
considerably like it's really thinking hard. Calculations and cursor/scroll
motion go into slow motion mode. I do not have other computer/application
problems - the behavior is unique to these actions. I have to complete close
Excel to regain the normal performance. Anything I can do to avoid? I
really need the DFunction capability.


No luck finding an answer? You could always try Google.