I HAVE CHECKED ITS WORKING BUT PROBLEM IS THAT THIS FORMULA IS ON MY TIPS
. SO THAT'S WHY I WANT ERROR HANDLER ON THIS
On Sat, Oct 1, 2011 at 10:18 PM, Sam Mathai Chacko <samde...@gmail.com>wrote:
> 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
>
--
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
