Well, I haven't used error handler in the first post, but if you use one of
the formulas from my second post, you will not get that #NUM error. Check
the attached file in my previous post.
Sam
On Sat, Oct 1, 2011 at 10:12 PM, Sourabh Salgotra <rhtdmja...@gmail.com>wrote:
> VERY VERY THANKS SIR FOR HELPING ME I HAVE ONE QUERY ALSO PENDING
>
> I WANT THAT WHICH CELLS HAVE NOT ANY RESULT THEY CAN DISPLAYED AS BLANK
> NOT AS #NUM IN THE FORMULA THAT I HAVE USED IN ATTACHED FILE.
>
>
>
>
>
> On Sat, Oct 1, 2011 at 4:15 PM, Sam Mathai Chacko <samde...@gmail.com>wrote:
>
>> By the way, replace "SINGH" with $C$3
>>
>>
>> On Sat, Oct 1, 2011 at 4:14 PM, Sam Mathai Chacko <samde...@gmail.com>wrote:
>>
>>> Don't seem like you tried my suggestion.
>>>
>>> Anyway, since there were posts sharing two examples with HLOOKUP and
>>> OFFSET, I have included that also in my suggestion.
>>>
>>> HLOOKUP and OFFSET will work, but not the way it was formulated it the
>>> previous post.
>>>
>>> Here's the working version of it in your sample file. I have left it in
>>> reverse order, and will leave you to figure out how to sort it in ascending
>>> order as I didn't think it was critical to your original query.
>>>
>>> For the mobile users, here's the array formulas used
>>>
>>> INDEX VERSION
>>>
>>>
>>> =IF(LARGE((ROW(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))*(ISNUMBER(SEARCH("SINGH",INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))))),ROW($A1))<>0,
>>> *INDEX*
>>> (INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$A$1:$A$100"),LARGE((ROW(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))*(ISNUMBER(SEARCH("SINGH",INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))))),ROW($A1))),"")
>>>
>>> HLOOKUP VERSION
>>>
>>> =IF(LARGE((ROW(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))*(ISNUMBER(SEARCH("SINGH",INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))))),ROW($A1))<>0,
>>> *HLOOKUP*
>>> (C$7,INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$A$1:$E$100"),LARGE((ROW(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))*(ISNUMBER(SEARCH("SINGH",INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))))),ROW($A1)),0),"")
>>>
>>> OFFSET VERSION
>>>
>>> =IF(LARGE((ROW(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))*(ISNUMBER(SEARCH("SINGH",INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))))),ROW($A1))<>0,
>>> *OFFSET*
>>> (INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$C$1"),LARGE((ROW(INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))*(ISNUMBER(SEARCH("SINGH",INDIRECT(TEXT(SEARCH!$D$1,"MMMYY")&"!$B$1:$B$100"))))),ROW($A1))-1,0),"")
>>>
>>> Regards,
>>>
>>> Sam Mathai Chacko (GL)
>>>
>>>
>>> On Sat, Oct 1, 2011 at 1:33 PM, Sourabh Salgotra
>>> <rhtdmja...@gmail.com>wrote:
>>>
>>>> not working
>>>>
>>>>
>>>> On Sat, Oct 1, 2011 at 10:40 AM, Sam Mathai Chacko
>>>> <samde...@gmail.com>wrote:
>>>>
>>>>> USE
>>>>>
>>>>>
>>>>> =INDEX(INDIRECT(TEXT(D1,"MMMYY")&"!$A$2:$A$65536"),SMALL(IF(ISNUMBER(SEARCH($C$3,INDIRECT(TEXT(D1,"MMMYY")&"!$B$2:$B$65536"))),ROW(INDIRECT(TEXT(D1,"MMMYY")&"!$B$2:$B$65536"))-MIN(ROW(INDIRECT(TEXT(D1,"MMMYY")&"!$B$2:$B$65536")))+1,""),ROW(A1)))
>>>>>
>>>>> in B8
>>>>>
>>>>> Regards
>>>>>
>>>>> Sam Mathai Chacko (GL)
>>>>>
>>>>>
>>>>> On Sat, Oct 1, 2011 at 8:03 AM, Saurabh Salgotra <rhtdmja...@gmail.com
>>>>> > wrote:
>>>>>
>>>>>> IN THIS SHEET SEARCHING IS WORKING BUT IT IS LIMITING FOR SEARCHING
>>>>>> THE DATA ONLY FROM ONE SHEET(WHICH IS ADDREDSSED IN FORMULA).
>>>>>>
>>>>>> I WANT TO SEARCH DATA FROM THE SHEET WHICH I HAVE SELECTED IN THE
>>>>>> DROP DOWN LIST
>>>>>>
>>>>>> I MEAN THAT WHICH SHEET IS SELECTED IN DROPDOWN LIST I GOT THE RESULT
>>>>>> FROM THAT SHEET.
>>>>>>
>>>>>>
>>>>>> FORMULA IS:
>>>>>>
>>>>>>
>>>>>> =INDEX(JUN12!$A$2:$A$65536,**SMALL(IF(ISNUMBER(SEARCH($C$3,**
>>>>>> JUN12!$B$2:$B$65536)),ROW(**JUN12!$B$2:$B$65536)-MIN(ROW(**
>>>>>> JUN12!$B$2:$B$65536))+1,""),**ROW(A1)))
>>>>>>
>>>>>>
>>>>>>
>>>>>> IN THIS FORMULA I HAVE MENTIONED THE SHEET NAME.
>>>>>>
>>>>>> SEE DROPDOWNLIST IN D1 I HAVE SELECTED THE SHEET DEC-10, HOW I CAN
>>>>>> WRITE THE FORMULA FOR THIS
>>>>>>
>>>>>> --
>>>>>>
>>>>>> ----------------------------------------------------------------------------------
>>>>>> Some important links for excel users:
>>>>>> 1. Follow us on TWITTER for tips tricks and links :
>>>>>> http://twitter.com/exceldailytip
>>>>>> 2. Join our LinkedIN group @
>>>>>> http://www.linkedin.com/groups?gid=1871310
>>>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>>>>
>>>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>>>
>>>>>> <><><><><><><><><><><><><><><><><><><><><><>
>>>>>> Like our page on facebook , Just follow below link
>>>>>> http://www.facebook.com/discussexcel
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Sam Mathai Chacko
>>>>>
>>>>> --
>>>>>
>>>>> ----------------------------------------------------------------------------------
>>>>> Some important links for excel users:
>>>>> 1. Follow us on TWITTER for tips tricks and links :
>>>>> http://twitter.com/exceldailytip
>>>>> 2. Join our LinkedIN group @
>>>>> http://www.linkedin.com/groups?gid=1871310
>>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>>>
>>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>>
>>>>> <><><><><><><><><><><><><><><><><><><><><><>
>>>>> Like our page on facebook , Just follow below link
>>>>> http://www.facebook.com/discussexcel
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> mujhay dukh is baat ka nahin kay meri zaat ko
>>>> muntashir karny walay haath tairy thy
>>>> mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko
>>>> samaitnay walay haath tairy na thy
>>>>
>>>>
>>>>
>>>> --------------------------------------------------------------------------------
>>>>
>>>>
>>>> Thanks & Regards
>>>> Sourabh
>>>> Contact Numbers: +91-94630-49202
>>>> Website:http://adhurapyaar.co.cc
>>>>
>>>>
>>>> --
>>>>
>>>> ----------------------------------------------------------------------------------
>>>> Some important links for excel users:
>>>> 1. Follow us on TWITTER for tips tricks and links :
>>>> http://twitter.com/exceldailytip
>>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>>
>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>
>>>> <><><><><><><><><><><><><><><><><><><><><><>
>>>> Like our page on facebook , Just follow below link
>>>> http://www.facebook.com/discussexcel
>>>>
>>>
>>>
>>>
>>> --
>>> Sam Mathai Chacko
>>>
>>
>>
>>
>> --
>> Sam Mathai Chacko
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> mujhay dukh is baat ka nahin kay meri zaat ko
> muntashir karny walay haath tairy thy
> mujhay dukh faqt is baat ka hay meri raiza raiza zaat ko
> samaitnay walay haath tairy na thy
>
>
>
> --------------------------------------------------------------------------------
>
>
> Thanks & Regards
> Sourabh
> Contact Numbers: +91-94630-49202
> Website:http://adhurapyaar.co.cc
>
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
Sam Mathai Chacko
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
