Re: $$Excel-Macros$$ SEARCHING DATA WITHIN SHEET WHICH IS SELECTED IN DROP DOWN LIST
On Sat, Oct 1, 2011 at 1:23 PM, Sourabh Salgotra rhtdmja...@gmail.comwrote: thanks for help but in this sheet all the data returned from the selected sheet searching not working. i want 2 solutions 1 choose sheet 2 search data (which is in c3 cols) if u type singh in c3 then result generated the all the record that contain singh displayed On Sat, Oct 1, 2011 at 12:08 PM, rajan verma rajanverma1...@gmail.comwrote: hi.. See the attached Sheet... Its Done With HlookUp + indirect and Other Way By offset() Regards Rajan. On Sat, Oct 1, 2011 at 8:03 AM, Saurabh Salgotra rhtdmja...@gmail.comwrote: 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 -- Regards Rajan verma +91 9158998701 -- -- 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 -- 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
Re: $$Excel-Macros$$ SEARCHING DATA WITHIN SHEET WHICH IS SELECTED IN DROP DOWN LIST
not working On Sat, Oct 1, 2011 at 10:40 AM, Sam Mathai Chacko samde...@gmail.comwrote: 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.comwrote: 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
Re: $$Excel-Macros$$ SEARCHING DATA WITHIN SHEET WHICH IS SELECTED IN DROP DOWN LIST
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.comwrote: 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.comwrote: By the way, replace SINGH with $C$3 On Sat, Oct 1, 2011 at 4:14 PM, Sam Mathai Chacko samde...@gmail.comwrote: 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.comwrote: not working On Sat, Oct 1, 2011 at 10:40 AM, Sam Mathai Chacko samde...@gmail.comwrote: 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
Re: $$Excel-Macros$$ SEARCHING DATA WITHIN SHEET WHICH IS SELECTED IN DROP DOWN LIST
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.comwrote: 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.comwrote: 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.comwrote: By the way, replace SINGH with $C$3 On Sat, Oct 1, 2011 at 4:14 PM, Sam Mathai Chacko samde...@gmail.comwrote: 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.comwrote: 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
Re: $$Excel-Macros$$ SEARCHING DATA WITHIN SHEET WHICH IS SELECTED IN DROP DOWN LIST
Shorter one... =IF(ROW($A1)COUNTIF(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9),*$C$3*),,INDEX(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$A$2:$A$9),SMALL(IF(ISNUMBER(SEARCH($C$3,INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9))),ROW(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9))-MIN(ROW(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9)))+1,),ROW(A1 Sam On Sat, Oct 1, 2011 at 10:28 PM, Sam Mathai Chacko samde...@gmail.comwrote: Interesting!!! Here you go... =IF(ISERROR(SMALL(IF(ISNUMBER(SEARCH($C$3,INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9))),ROW(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9))-MIN(ROW(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9)))+1,),ROW(A1))),,INDEX(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$A$2:$A$9),SMALL(IF(ISNUMBER(SEARCH($C$3,INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9))),ROW(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9))-MIN(ROW(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9)))+1,),ROW($A1 Sam On Sat, Oct 1, 2011 at 10:21 PM, Sourabh Salgotra rhtdmja...@gmail.comwrote: 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.comwrote: 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.comwrote: 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.comwrote: By the way, replace SINGH with $C$3 On Sat, Oct 1, 2011 at 4:14 PM, Sam Mathai Chacko samde...@gmail.comwrote: 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 @
Re: $$Excel-Macros$$ SEARCHING DATA WITHIN SHEET WHICH IS SELECTED IN DROP DOWN LIST
very very thanks sir for helping me. On 10/1/11, Sam Mathai Chacko samde...@gmail.com wrote: Shorter one... =IF(ROW($A1)COUNTIF(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9),*$C$3*),,INDEX(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$A$2:$A$9),SMALL(IF(ISNUMBER(SEARCH($C$3,INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9))),ROW(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9))-MIN(ROW(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9)))+1,),ROW(A1 Sam On Sat, Oct 1, 2011 at 10:28 PM, Sam Mathai Chacko samde...@gmail.comwrote: Interesting!!! Here you go... =IF(ISERROR(SMALL(IF(ISNUMBER(SEARCH($C$3,INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9))),ROW(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9))-MIN(ROW(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9)))+1,),ROW(A1))),,INDEX(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$A$2:$A$9),SMALL(IF(ISNUMBER(SEARCH($C$3,INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9))),ROW(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9))-MIN(ROW(INDIRECT(TEXT(SEARCH!$D$1,MMMYY)!$B$2:$B$9)))+1,),ROW($A1 Sam On Sat, Oct 1, 2011 at 10:21 PM, Sourabh Salgotra rhtdmja...@gmail.comwrote: 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.comwrote: 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.comwrote: 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.comwrote: By the way, replace SINGH with $C$3 On Sat, Oct 1, 2011 at 4:14 PM, Sam Mathai Chacko samde...@gmail.comwrote: 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
Re: $$Excel-Macros$$ SEARCHING DATA WITHIN SHEET WHICH IS SELECTED IN DROP DOWN LIST
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.comwrote: 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
