Free Microsoft Excel 2013 Quick Reference

- Use Formula in DGET criteria
- Help on DGET Function
- How to indentify duplicates in a "Self Populating" field?
- Use index to get range for dget to use
- Use Index to get range
- DGET - Formula Returns #NUM! Error (I verified no duplicates)
- Database Function Criteria range
- Enter Quotes & Equal Signs In Cells Code
- Formulas: List multiple returns
- Lookup using multiple criteria in columns
- Dget...problems
- Replacing VLOOKUP with DGET
- Arrays & array formulas
- At my wit's end! Use DGET or VLOOKUP to select data????
- Is there a function I can use to pull in data to this table?
- Return Multiple Values as a List Using Multiple Criteria
- Dynamic top 10 list
- Retrieving data from a database list

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

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!

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.

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.

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 a range called rangearray

If I do the index function independently it gives me the correct range but

in the dget formula I get an error?

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 REALLY hoping you can help me out here. I am trying to use the function DGET() to re-organize the information on the "DbCalc" sheet in the attached workbook. This formula can be found in C2:C5 in "purple. I am referencing the worksheet "Exp Rpt" as a database and match info based on two criteria. I can do this using an array formula like this ={SUM(IF((A1='Exp Rpt'!B$9:B$10000)*(A2='Exp Rpt'!E$9:E$10000),'Exp Rpt'!H$9:H$10000,0))} , however with the amount of data that I need to reference this array is soaking up all my memory. I would usually turn to access to do this, but the person that will be utilizing this tool only knows excel. Any suggestions? Thank you sooooo much

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:This didn't work either because VBA interpreted it as a TRUE/FALSE test whether the = sign was = to the partnumber (always false)CriteriaRange.Cells(2, 1).Value = "=" & """ & " = " & sItem & """If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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

My data is in the following format

Ref 1 Field 1 Field 2 Field 3 Output

Ref 2 Field 1 Field 2 Field 3 Output

Ref 3 Field 1 Field 2 Field 3 Output

where I want the output coumn to look across Field1..Field 3, and match all of them to the first 3 columns of an array, and return the value in the designated column number. Tried the database functions (DGET,DSUM etc) but they require the headers in the formula which only works for the first row.

I believe I didnt state my problem correctly in my original post. I am converting array formulas by using dget (which i have read would be faster). I have all my criteria coming in right. the only problem I have in solving is I have one column with multiple portfoilio numbers and another with portfolio names. Soooo, I could have several portfolio numbers to one portfolio name.

My dget is based off of portfolio names so does anyone know how i can incorporate some sort of match or index where i can make all this work w/o an array?

Thanks in advance for your help.

Latigo

=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.

{=SUM((DB_VARIABLE=$CB$51)*(DB_YEAR=CA$5)*(DB_JANVALUES)}

What I was wondering about the array formula is whether there is a way to avoid having to name a separate array for each month, as well as DB_VARIABLE and DB_YEAR, for a total of 14 arrays.

(In 1-2-3, I can use the @DGET function to do this putting the criteria directly into the function and all I have to do is name the database range with column headings in one step.)

Or can anyone think of a better way to obtain the value I want from the database using Excel?

spreadsheet. On worksheet X, there are two columns and 1100 rows. Column A

is a list of store numbers sorted in ascending order, but they are not

sequential. Column B is the corresponding name of the store. On worksheet

Y, I want to type in the number of a store in a long list of rows under

column A and return the name of the store in the same row in column B.

I can get the VLOOKUP formula to work, but with this problem: if I type in a

store number on worksheet Y that does not appear at all on worksheet X, it

will return a store name anyway. I want to make sure that a store number

entered on worksheet Y is valid, otherwise, return an error.

DGET works perfectly to detect an error, but it will not work in a row by

row format; I have to have a column heading and a single cell below for the

criteria (store number)

--

Allen

I want to use a formula to populate the table in the "extract" tab from values in the "cardiac output" column from the "EchoTest" tab based on the criteria that (!Echotest[PT]=!extractA3) AND (!EchoTest[Corrected Vector]=!ExtractB2)

Does that make sense?

I can't use VLOOKUP and I'd hoped DGET would help me but I can't see how to make it do what I'm asking.

Any help here would be greatly appreciated, I'm losing hours of my life and frustration levels are peaking!

Thanks

Marcus

Here's my situation. I have a database with multiple columns. I am interested in retrieving Column D's information only when Columns J contains text "Enrolled" AND Column U contains "0". The results (any value in Column D where this is true) would be presented in the form of a list.

The goal of this formula would be a sort of quality control measure. Any ideas? I've thought of using the Index formula or the DGet formula, but not sure if either one would fit this scenario.

Thanks,

Kevin

col A: group identifier (e.g., A, B, C)

col B: unique name/ID

col c: score

col d: rank -- generated using {=(COUNTIF($A$2:$A$254,$A2)-SUM(IF(($C$2:$C$254>=$C2)*($A$2:$A$254=$A2),1,0))+1)}

As you can see, the rank is generated dynamically such that I can add new records to the bottom of the list, and all ranks will be updated. Also note that rankings are within each group. For example, there is a number 1 ranking for group A, as well as a number 1 ranking for group B.

Alright, thats the data setup; onto the report I'd like to generate. I'm trying to generate a 3 column report:

col f: group (an identifier for my conditions)

col g: ranks (will eventually be 1-10)

col h: name/ID of the record meeting the criteria of a particular group (specified in col f) and a particular rank (specified in col g).

My goal is to dynamically output in column H the name of the record which has the rank specified in column G within each grouping that is specified in column F. Thus, cell H2 needs to return ID10, because this record has a rank of 1 for group A. In the same way, H3 should return ID5, H4 should return ID9, etc.

So far, I've played around with vlookup and dget without success because I can't figure out how to include conditions in these statements via nested formulas. I've also contemplated a pivot table, but I'm not sure how to make that work.

If anyone has advice, I'd really appreciate your assistance.

Thanks,

Ryan

------------------------------------------------------

Here is a visual representation of what I currently have:

col Acol Bcol Ccol Dcol Fcol Gcol HGroupNameNumberRankGroupRankNameCID1110352A1CID2208873A2AID3516896B1DID4530004B2AID5103432C1DID6412222C2CID73001D1DID8204351D2BID9113041AID1090001AID11125463AID12234784DID13489323BID14220092AID15406575BID16513433DID171000005AID18768597BID19705574

I would like to know how to retrieve one value from a database list based on

multiple criteria. My list as the following headings:

Company, Year, Revenue ($)

I would like to perform some form of lookup function to retrieve a unique

value based on several criteria.

E.g. If Company = "company 1" and Year = "2000", return the value for

"Revenue ($)".

Bear in mind that there are several hundred company names, and that I would

like to be able to copy this formula into other cells, referencing different

criteria.

I've tried DGET, but can't work out how to use.

Any suggestions would be much appreciated.

RA