| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DGET lookup failing
I'm having a strange issue with DGET returning #NUM! for a particular
criteria value but not others. The values in the criteria field are as follows: Periods: Month & YTD v Budget Periods: YTD & Year v Budget Periods: Month, YTD, YTG & Next Yr Periods: Rolling 12 Months Periods: Rolling 12 Months plus 2 Periods: Rolling 24 Months Entities: TIG Consolidated DGET will successfully return a value when the value in the criteria is ANY value OTHER than "Periods: Rolling 12 Months". If I use that value in the criteria it returns #NUM!. It seems to somehow be getting confused due the presence of another value in the field that is similar ("Periods: Rolling 12 Months plus 2") since if I change the "Periods: Rolling 12 Months plus 2" to any other value, DGET will successfully return a value using "Periods: Rolling 12 Months" as the criteria. I am really stuumped on this one.... |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DGET lookup failing
Hi,
Can you show the data and the formula you are writing -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mitch Powell" wrote in message ... I'm having a strange issue with DGET returning #NUM! for a particular criteria value but not others. The values in the criteria field are as follows: Periods: Month & YTD v Budget Periods: YTD & Year v Budget Periods: Month, YTD, YTG & Next Yr Periods: Rolling 12 Months Periods: Rolling 12 Months plus 2 Periods: Rolling 24 Months Entities: TIG Consolidated DGET will successfully return a value when the value in the criteria is ANY value OTHER than "Periods: Rolling 12 Months". If I use that value in the criteria it returns #NUM!. It seems to somehow be getting confused due the presence of another value in the field that is similar ("Periods: Rolling 12 Months plus 2") since if I change the "Periods: Rolling 12 Months plus 2" to any other value, DGET will successfully return a value using "Periods: Rolling 12 Months" as the criteria. I am really stuumped on this one.... |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DGET lookup failing
I'll send the data when I get back to the office Tuesday. I can you that I
experimented a bit more and found that it appears that when items in the criteria field share identiacal first characters (but are different in total), the function fails because it thinks there are duplicates. For example, if both Pear and Pears exist in the in criteria column and Pear is the criteria value, the function returns #NUM!. "Ashish Mathur" wrote: Hi, Can you show the data and the formula you are writing -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mitch Powell" wrote in message ... I'm having a strange issue with DGET returning #NUM! for a particular criteria value but not others. The values in the criteria field are as follows: Periods: Month & YTD v Budget Periods: YTD & Year v Budget Periods: Month, YTD, YTG & Next Yr Periods: Rolling 12 Months Periods: Rolling 12 Months plus 2 Periods: Rolling 24 Months Entities: TIG Consolidated DGET will successfully return a value when the value in the criteria is ANY value OTHER than "Periods: Rolling 12 Months". If I use that value in the criteria it returns #NUM!. It seems to somehow be getting confused due the presence of another value in the field that is similar ("Periods: Rolling 12 Months plus 2") since if I change the "Periods: Rolling 12 Months plus 2" to any other value, DGET will successfully return a value using "Periods: Rolling 12 Months" as the criteria. I am really stuumped on this one.... |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DGET lookup failing
The data is as follows:
Field1 Field2 Periods: Month & YTD v Budget ColView_1 Periods: YTD & Year v Budget ColView_2 Periods: Month, YTD, YTG & Next Yr ColView_3 Periods: Rolling 12 ColView_4 Periods: Rolling 12 Months plus 2 ColView_5 Periods: Rolling 24 Months ColView_6 Entities: TIG Consolidated ColView_7 The criteria range is as follows: Field1 Periods: Rolling 12 DGET returns #NUM!. If I remove the row containing "Periods: Rolling 12 Months plus 2", it works just fine. "Ashish Mathur" wrote: Hi, Can you show the data and the formula you are writing -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mitch Powell" wrote in message ... I'm having a strange issue with DGET returning #NUM! for a particular criteria value but not others. The values in the criteria field are as follows: Periods: Month & YTD v Budget Periods: YTD & Year v Budget Periods: Month, YTD, YTG & Next Yr Periods: Rolling 12 Months Periods: Rolling 12 Months plus 2 Periods: Rolling 24 Months Entities: TIG Consolidated DGET will successfully return a value when the value in the criteria is ANY value OTHER than "Periods: Rolling 12 Months". If I use that value in the criteria it returns #NUM!. It seems to somehow be getting confused due the presence of another value in the field that is similar ("Periods: Rolling 12 Months plus 2") since if I change the "Periods: Rolling 12 Months plus 2" to any other value, DGET will successfully return a value using "Periods: Rolling 12 Months" as the criteria. I am really stuumped on this one.... |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DGET lookup failing
Hi,
Try this. In the criteria range, delete the heading I.e. Field1. In the next cell of the criteria range, where you have typed Periods: Rolling 12, enter the following formula =EXACT("Periods: Rolling 12",B5) where B5 is the first cell (below the heading) in the range Now the DGET will return the right answer -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mitch Powell" wrote in message ... The data is as follows: Field1 Field2 Periods: Month & YTD v Budget ColView_1 Periods: YTD & Year v Budget ColView_2 Periods: Month, YTD, YTG & Next Yr ColView_3 Periods: Rolling 12 ColView_4 Periods: Rolling 12 Months plus 2 ColView_5 Periods: Rolling 24 Months ColView_6 Entities: TIG Consolidated ColView_7 The criteria range is as follows: Field1 Periods: Rolling 12 DGET returns #NUM!. If I remove the row containing "Periods: Rolling 12 Months plus 2", it works just fine. "Ashish Mathur" wrote: Hi, Can you show the data and the formula you are writing -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mitch Powell" wrote in message ... I'm having a strange issue with DGET returning #NUM! for a particular criteria value but not others. The values in the criteria field are as follows: Periods: Month & YTD v Budget Periods: YTD & Year v Budget Periods: Month, YTD, YTG & Next Yr Periods: Rolling 12 Months Periods: Rolling 12 Months plus 2 Periods: Rolling 24 Months Entities: TIG Consolidated DGET will successfully return a value when the value in the criteria is ANY value OTHER than "Periods: Rolling 12 Months". If I use that value in the criteria it returns #NUM!. It seems to somehow be getting confused due the presence of another value in the field that is similar ("Periods: Rolling 12 Months plus 2") since if I change the "Periods: Rolling 12 Months plus 2" to any other value, DGET will successfully return a value using "Periods: Rolling 12 Months" as the criteria. I am really stuumped on this one.... |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DGET lookup failing
It worked! Thanks but I have no idea why it worked. Inquiring minds want to
know... :-) "Ashish Mathur" wrote: Hi, Try this. In the criteria range, delete the heading I.e. Field1. In the next cell of the criteria range, where you have typed Periods: Rolling 12, enter the following formula =EXACT("Periods: Rolling 12",B5) where B5 is the first cell (below the heading) in the range Now the DGET will return the right answer -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mitch Powell" wrote in message ... The data is as follows: Field1 Field2 Periods: Month & YTD v Budget ColView_1 Periods: YTD & Year v Budget ColView_2 Periods: Month, YTD, YTG & Next Yr ColView_3 Periods: Rolling 12 ColView_4 Periods: Rolling 12 Months plus 2 ColView_5 Periods: Rolling 24 Months ColView_6 Entities: TIG Consolidated ColView_7 The criteria range is as follows: Field1 Periods: Rolling 12 DGET returns #NUM!. If I remove the row containing "Periods: Rolling 12 Months plus 2", it works just fine. "Ashish Mathur" wrote: Hi, Can you show the data and the formula you are writing -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mitch Powell" wrote in message ... I'm having a strange issue with DGET returning #NUM! for a particular criteria value but not others. The values in the criteria field are as follows: Periods: Month & YTD v Budget Periods: YTD & Year v Budget Periods: Month, YTD, YTG & Next Yr Periods: Rolling 12 Months Periods: Rolling 12 Months plus 2 Periods: Rolling 24 Months Entities: TIG Consolidated DGET will successfully return a value when the value in the criteria is ANY value OTHER than "Periods: Rolling 12 Months". If I use that value in the criteria it returns #NUM!. It seems to somehow be getting confused due the presence of another value in the field that is similar ("Periods: Rolling 12 Months plus 2") since if I change the "Periods: Rolling 12 Months plus 2" to any other value, DGET will successfully return a value using "Periods: Rolling 12 Months" as the criteria. I am really stuumped on this one.... |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DGET lookup failing
Hi,
When you mention the criteria as Periods: Rolling 12, you are in effect saying that find all cells which contain Periods: Rolling 12. To find for the exact word, I used the exact function which compares whether the strings are same or not. Since this formula will evaluate to TRUE/FALSE, I have given it a heading other then the heading of your source data (Read up on Database functions in the Help menu). Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mitch Powell" wrote in message ... It worked! Thanks but I have no idea why it worked. Inquiring minds want to know... :-) "Ashish Mathur" wrote: Hi, Try this. In the criteria range, delete the heading I.e. Field1. In the next cell of the criteria range, where you have typed Periods: Rolling 12, enter the following formula =EXACT("Periods: Rolling 12",B5) where B5 is the first cell (below the heading) in the range Now the DGET will return the right answer -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mitch Powell" wrote in message ... The data is as follows: Field1 Field2 Periods: Month & YTD v Budget ColView_1 Periods: YTD & Year v Budget ColView_2 Periods: Month, YTD, YTG & Next Yr ColView_3 Periods: Rolling 12 ColView_4 Periods: Rolling 12 Months plus 2 ColView_5 Periods: Rolling 24 Months ColView_6 Entities: TIG Consolidated ColView_7 The criteria range is as follows: Field1 Periods: Rolling 12 DGET returns #NUM!. If I remove the row containing "Periods: Rolling 12 Months plus 2", it works just fine. "Ashish Mathur" wrote: Hi, Can you show the data and the formula you are writing -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mitch Powell" wrote in message ... I'm having a strange issue with DGET returning #NUM! for a particular criteria value but not others. The values in the criteria field are as follows: Periods: Month & YTD v Budget Periods: YTD & Year v Budget Periods: Month, YTD, YTG & Next Yr Periods: Rolling 12 Months Periods: Rolling 12 Months plus 2 Periods: Rolling 24 Months Entities: TIG Consolidated DGET will successfully return a value when the value in the criteria is ANY value OTHER than "Periods: Rolling 12 Months". If I use that value in the criteria it returns #NUM!. It seems to somehow be getting confused due the presence of another value in the field that is similar ("Periods: Rolling 12 Months plus 2") since if I change the "Periods: Rolling 12 Months plus 2" to any other value, DGET will successfully return a value using "Periods: Rolling 12 Months" as the criteria. I am really stuumped on this one.... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Failing with formula | Excel Discussion (Misc queries) | |||
| Failing IF Statement | Excel Discussion (Misc queries) | |||
| COUNTA failing? | Excel Discussion (Misc queries) | |||
| My formulas are failing! | Excel Discussion (Misc queries) | |||
| Link 'failing' | Links and Linking in Excel | |||
Linear Mode
