Using cell reference with logical operator in DGET expression

I am using DGET to search an array in the worksheet to find a particular 
percent to use in a calculation elsewhere in the spreadsheet. The row members 
of the array contain a series of from and to values that I use to identify 
which row has the percent I am looking for. I have defined the array as a 
range and I can use the logical operatirs with numeric values in the range 
criteria and everything works fine. For example, I can use <500 in the 
appropriate cell in range criteria to find the percent to use when the value 
for that column in the array is less than 500. All working fine. If, however, 
I use a cell reference in the criteria range where the value I want to tet is 
coming from, for example cell +V2 in the worksheet, the only logical operator 
I seem to be able to use is =. For example, if I enter =V2 in the criteria 
range, the DGET expression resolves properly. But if I enter <V2 or >V2 or 
<=V2 or <=V2, I get a VALUE error. In order to resolve for the correct 
percent I have to be able to test multiple criteria for "greater than" Col A 
and "less than Col B, and so forth. And the values I am testing against are 
coming from an import into the spreadsheet. So what I really need is the 
ability to take an imported value which is located in a cell and configure 
the range criteria to find the row in the array where that value fits between 
the values in ColA and ColB, etc. Can this be done with DGet? 
0
Utf
4/28/2010 10:20:05 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
1476 Views

Similar Articles

[PageSpeed] 9

RE:  But if I enter <V2 or >V2 or
<=V2 or <=V2, I get a VALUE erro

Use;

 =">" & V2 etc.



"BoxleyFarm" <BoxleyFarm@discussions.microsoft.com> wrote in message 
news:6AEF2DCA-8D0A-4230-9052-40958384FD4A@microsoft.com...
>I am using DGET to search an array in the worksheet to find a particular
> percent to use in a calculation elsewhere in the spreadsheet. The row 
> members
> of the array contain a series of from and to values that I use to identify
> which row has the percent I am looking for. I have defined the array as a
> range and I can use the logical operatirs with numeric values in the range
> criteria and everything works fine. For example, I can use <500 in the
> appropriate cell in range criteria to find the percent to use when the 
> value
> for that column in the array is less than 500. All working fine. If, 
> however,
> I use a cell reference in the criteria range where the value I want to tet 
> is
> coming from, for example cell +V2 in the worksheet, the only logical 
> operator
> I seem to be able to use is =. For example, if I enter =V2 in the criteria
> range, the DGET expression resolves properly. But if I enter <V2 or >V2 or
> <=V2 or <=V2, I get a VALUE error. In order to resolve for the correct
> percent I have to be able to test multiple criteria for "greater than" Col 
> A
> and "less than Col B, and so forth. And the values I am testing against 
> are
> coming from an import into the spreadsheet. So what I really need is the
> ability to take an imported value which is located in a cell and configure
> the range criteria to find the row in the array where that value fits 
> between
> the values in ColA and ColB, etc. Can this be done with DGet? 

0
ozgrid
4/28/2010 10:31:16 AM
Reply:

Similar Artilces:

MS RMS Store Operations Study Guide?
Does any one have this? I would like to get certified but I don't have the $298 to buy it. http://www.microsoft.com/businesssolutions/retailmanagementsystem/using/rms_elearning.mspx Thanks, Jho After purchasing RMS in January I was told that the User's Guide (which could bethe basis of study) was no longer available in printed form. After finally gaining access to CustomerSource, I found it available for download (all 536 pages of it). Yesterday, my RMS tech told me that it is again being shipped with new purchases of RMS. You do have to be a support customer to access the s...

Prevent clicking on a cell
I want to run the code below to prevent a range of cells from being selected if the Range("Q7") = 1. I have all cells on the worksheet locked but the user must be able to click on the locked cells to trigger a userform so I have to check Select Locked Cells. So is there any way make the Range("B5:C5") unselectable? If Range("Q7") = 1 Then Range("B5:C5").Locked = True End If Hi, >So is there any way make the > Range("B5:C5") unselectable? No but you can stop them staying there. Private Sub Worksheet_...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

Outlook Express #135
Is there any problem with using Outlook Express for e mail retrieval but also keeping Outlook open at the same time for the calendar and task lists? I am doing this because Express has auto fill of the e mail address and Outlook 2000 does not. Also, is there a way to copy my contacts from Outlook to Outlook Express? Thanks. You can try: - exporting your Outlook contacts to a CSV file for import into OE - upgrading to OL2002 or 2003 since they both have auto-complete like OE "carolyn" <anonymous@discussions.microsoft.com> wrote in message news:292a01c3e143$d4619810$a0012...

INTERNET MILLION DOLLARS
INTERNET MILLION DOLLARS - www.InternetMillionDollars.biz Watch the VIDEO on YOUTUBE... NOW! http://www.youtube.com/watch?v=dXQUjk5EGV0 MAKE MONEY INSTANTLY using 2007 featured INTERNET MILLION DOLLARS as a resource. WORK FROM HOME HOMEBASED JOBS AND IDEAS BUSINESS OPPORTUNITY Professor James Bradley's "Quick and real way to make BIG MONEY on the Internet!" The Independent Finally you have FOUND IT! Make a FORTUNE each month... .... every month! 100% ON AUTOPILOT Read it NOW and CHANGE YOUR LIFE! Are you fed up with being short of MONEY all the time? Do you DREAM about a...

Can SUMPRODUCT be used for entire column?
This formula results in a numeric result: =SUMPRODUCT((A1:A20)*(B1:B20="b")) but this formula results in a #NUM! result: =SUMPRODUCT((A:A)*(B:B="b")) Which means I need to specify the length of the columns, which may grow over time. Any way to do this for the entire column, without having to specify the length of the column? XL07 removed the limitation on array formulas (which SUMPRODUCT is, even though it doesn't require CTRL-SHIFT-ENTER) and entire columns. For pre-XL07, one can use =SUMPRODUCT(A1:A65535, --(B1:B65536="b")) to get all but on...

How do I extend a underline across an entire cell?
When working on a financial statement, I was curious how to 1. Have a line extend across an entire cell even if the number is only 2-3 digits and 2. How to apply a double line under a number without using the = sign in the following cell? Hi Lindsay Look on the formatting toolbar for Borders -- Regards Ron de Bruin http://www.rondebruin.nl "Lindsay" <Lindsay@discussions.microsoft.com> wrote in message news:F4C9ED6C-7F2D-4277-86CC-6FA46D315DA5@microsoft.com... > When working on a financial statement, I was curious how to 1. Have a line > extend across an entire ce...

Outlook Express and LAN connection
I have Outlook Express 6.00.280 installed on Win NT 4.0 SP6 which is LAN connected to another PC that has Internet connection (Proxy server). Email account on my PC is POP3 for incoming mails and SMTP for outcoming. Now the problem is that I cannot receive emails but can only send them through this Proxy. Internet Explorer and all other applications that require Internet connection through proxy are working OK. The only problem I have is to receive emails. I setup the same account on another PC which has direct connection to the Internet through dialup and no problems for both sending and rece...

Using scanner in Word97
I want to scan a picture into word97 using my HP 4370 ScanJet. Could not find option of "From Scanner" under "Insert" --> "Picture". Apparently I must need some sort of Word97 Add-On. What and where is the add-on? Is it on the Office97 CD? Same applys to Excel97. Also, does microsoft sponsor a Word97 / Office97 discussion group? If so, would appreciate a link. "PSRumbagh" <PSRumbagh@discussions.microsoft.com> said this in news item news:39100DC1-A7EE-4679-881D-526BAA386620@microsoft.com... > I want to scan a picture ...

Using later version of microsoft access
Hi, I've got access 2000 on my computer. When I go to open a database someone sent me I get an error message : this database is in an unrecognized format. The database may have been created with a later version of microsoft access. Is there any way I can open and use this file (short of upgrading to later version of access)? If you do not have Access 2002 or 2003, ask the person to save it in Access 2000 format for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at m...

Reference Book
I'm a Win32 programmer, and I'm trying to get into some MFC. I've read a little about MFC, but the books lack a lot of quality and descriptive text about the structure of MFC. Can I ask whose book is the Charles Petzold of MFC? Thank you I haven't seen a bad MFC book in a long time, but then again, I haven't bought a new MFC book in many years. My presonal perference are the MFC books published by Wrox Press http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y&isbn=1861000855&itm=31 You can't go wrong with the Microsoft Press ones either. Al...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

cell contents revert to 0 when i click on the next cell
I put a number into a cell click on the next cell and the first cell reverts to 0. If I format to number with 2 decimal places it will be ok but when I try to take out decimal places it goes back to zero, Help please You haven't said what number you are trying to put into the cell, but I suspect that the number is less than 0.5. A quick test shows that if you set the cell to no decimal places then enter a number less than 0.5 it is displayed 'rounded down' so it will show as zero, if it's 0.5 or above it displays as 1. If you need to put numbers less than 0.5 into youe c...

Using Company Wide Mail Templates.
Hi, I do not know if i am at the right spot here, or if it is evne possible, but i got the following question. My boss would like me to make sure that every outgoing mail has the same looks. It starts by adding a signature that is the same for everyone, except with ofcourse personalized information. This was easily done by giving everyone a signature. The next question is however, to put the head of our website, also above our mail. This means that every user that sends a mail, the mail will have a nice header, underneath that header, the mail is typed, and then its ended with the si...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

how create Quota filter in WIndows 2003 R2 using Script
Hi all I need create quota filter in Server 2003 R2 using vbscript. quota filter should be applied to directories and not by users. I searched information about it on google without success. thank's in advance Daniel Hi Daniel. First you need open your FSRM (File Server Resource Manager), then you create a quota template, you must specify if your quota is "software" (just monitoring, but never deny the user) or "hardaware" (deny users when they use 100% of the quota), you must specify if you want send e-mail to user when this user use...

Automatic changes in cells
Hi for some reason I now have to save my work for any formlas etc to change when I update a worsheet, how can I stop this as it is a pain and sometimes I need to do changes to see how they work before saving the work. Many thanks Click on Tools | Options | Calculation tab and set to Automatic calculation, as it is probably set to Manual. You can press F9 to force a recalculation under a manual setting. Make sure you save the file with the Automatic setting, to avoid it happening next time. Hope this helps. Pete On Feb 1, 11:42=A0am, Office 2004 Test Drive User <heepenm...@yahoo.co.u...

Outlook with Outlook Express ?
Is it possible to select an Outlook Contact, and send an email using Outlook Express? Thanks. Talal Itani No. But you can do it vice versa. "Talal Itani" <titani@verizon.net> wrote in message news:TXaXh.4742$Fc1.4027@trnddc05... > > Is it possible to select an Outlook Contact, and send an email using > Outlook Express? > > Thanks. > Talal Itani > Talal Itani <titani@verizon.net> wrote: > Is it possible to select an Outlook Contact, and send an email using > Outlook Express? No, but you could open the Outlook Contact, select the e...

Missing Reference
Argh! There was a missing reference, " Microsft DAO2.5/3.5 Compact ..." I unselected it and backed out - now, every date field is filled with today's date and several other fields have gone to the 'dark side'. The $ field shows $0.00 for everyone and another field is blank. I went back into the dialog box and I can't find that reference in the list to reselect it. Thank goodness I experimented using a copy, eh? >-----Original Message----- >Your references are probably messed up. >If any of the selected references have "MISSING:" in front ...

cell colour change when set markers are reached
i need to get a cell to change colour when markers are reached eg a qualification lasts 12 months. what i want to do is have the cell change from yellow to orange to red as the expiry date gets closer. If column A contains expiry dates then select column A, Formats>Conditional Formatting>formula1: =DATEDIF(TODAY(),A1,"m")<1 red for 1 month Click Add button, formula2: =DATEDIF(TODAY(),A1,"m")<2 orange for 2 month Click Add button, formula3: =DATEDIF(TODAY(),A1,"m")<3 yellow for 3 month Adjust number of months as you like! Regards,...

OutLook Express #229
I get an error message I cannot seem to get rid of for mail to be sent. However, there's nothing in my sent folder. How can I refresh my outlook and/or delete the 2 original messages I tried sending if the messages are no longer in my 'outgoing' folder? The emails aren't in either the outgoing or sent folders. But when I click on the send/receiv button the comment "sending 1 or 2 ....". Only I can't find the emails to delete them. Ask in an Outlook Express forum. This is an Microsoft Office Outlook forum. Outlook Express is family of Internet Explore...

Using Publisher 200 with Publisher 2003
How do I covert PUB2000 documents to Pub2003 documents and vice versa? Pub 2003 can open anything, no conversion necessary. Going backward is a bit trickier. File - Save As and chance the file type to a Pub 2000 file. Possible problems can arise if you've used a feature that was not available in the 2000 version and your file size will grew immensely. -- JoAnn Paules MVP Microsoft [Publisher] "nasuco" <nasuco@discussions.microsoft.com> wrote in message news:500C7A7A-4026-434C-8CC2-2DFDB69D81C4@microsoft.com... > How do I covert PUB2000 documents to Pub2003 do...

Calculating on alphabetic cell content
Hi, A selection of 4 different letters in a column representing different values to be used in a formula shall be run through. The calculated result of each cell in the column shall be placed in the cell next to the read one that holds the letter. Thanks in advance. Hi i think you're after the COUNTIF function with your column of letters in A1:A100 and the letter you're interested in in C1 then in D1 =COUNTIF(A1:A100,C1) this will count the number of times the value in C1 occurs in your range. If this isn't what you're after, could you type out a few examples of your ...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...