Hi Manish, You are welcome!!! and in my previous reply I wrongly mentioned the name as "Bhushan" instead of your name . ----------------------- Ms.Exl.Learner -----------------------
On Sun, Feb 6, 2011 at 2:38 PM, Manish <pansari.man...@gmail.com> wrote: > Hey... Gr8... > Thanks for reply... > You all are just Wow !!! !! > > Regds, > Manish > > On Feb 6, 2:01 pm, "Ms-Exl-Learner ." <ms.exl.lear...@gmail.com> > wrote: > > Hi Bhushan, > > > > Sumproduct can be used when the result is Numeric Value. > > > > Have a look in the attached excel for retrieving the text output. > > > > ----------------------- > > Ms.Exl.Learner > > ----------------------- > > > > > > > > > > > > On Sat, Feb 5, 2011 at 12:09 PM, Manish <pansari.man...@gmail.com> > wrote: > > > Also Sumproduct is useful for numaric value. > > > Is there any other formula, that will work as SUMPRODUCT for text?? > > > > > Pls suggest. > > > > > On Feb 5, 11:05 am, Manish <pansari.man...@gmail.com> wrote: > > > > Is it possible to hide the complete row based on lookup value?? > > > > For example in your solution, if i lookup value x than i want to hide > > > > row 14 because the lookup result is 0 /Blank. And if i change the > > > > value and look the value y i want to hide both row 13 and 14. > > > > > > Simply, I want automtion for hide blank rows. > > > > pls suggest the code OR upload the example file for reference. > > > > > > Thanks !! > > > > > > - > > > > Manish > > > > > > On Feb 2, 6:41 pm, ashish koul <koul.ash...@gmail.com> wrote: > > > > > > > @bushan > > > > > > > mail your file to id excel-macros@googlegroups.com and attach > the > > > file > > > > > > > On Wed, Feb 2, 2011 at 4:42 PM, Bhushan <bsabban...@gmail.com> > wrote: > > > > > > Hi, > > > > > > > > I am not finding any link from where i can upload my sample file. > > > > > > > > Regards. > > > > > > Bhushan > > > > > > > > On Feb 2, 2:11 pm, Manish <pansari.man...@gmail.com> wrote: > > > > > > > Thanks for valuable post. > > > > > > > Is it possible to hide the complete row based on lookup value?? > > > > > > > For example in your solution, if i lookup value x than i want > to > > > hide > > > > > > > row 14 because the lookup result is 0 /Blank. And if i change > the > > > > > > > value and look the value y i want to hide both row 13 and 14. > > > > > > > > > pls suggest the code OR upload the example file for reference. > > > > > > > > > Thanks !! > > > > > > > > > - > > > > > > > Manish > > > > > > > > > On Feb 1, 11:23 pm, "Ms-Exl-Learner ." < > ms.exl.lear...@gmail.com> > > > > > > > wrote: > > > > > > > > > > Hi Bhushan, > > > > > > > > > > Have a look in the attached files. > > > > > > > > > > I never suggest array formula when the same can be done in > normal > > > way. > > > > > > > > > > HTH :) > > > > > > > > > > ----------------------- > > > > > > > > Ms.Exl.Learner > > > > > > > > ----------------------- > > > > > > > > > > On Tue, Feb 1, 2011 at 6:22 PM, Bhushan < > bsabban...@gmail.com> > > > wrote: > > > > > > > > > Dear Ayush, > > > > > > > > > > > Pls assist me for the below formula. I have a huge data > from > > > that I > > > > > > > > > have given a sample below for your understanding. I have a > data > > > with > > > > > > > > > lookup value with different corresponding values. When I am > > > using the > > > > > > > > > vlookup the vlookup is only taking the one correponding > value > > > of that > > > > > > > > > lookup I am using the below formula for the diffrenet > > > corresponding > > > > > > > > > values the problem is when the lookup value changes the > formula > > > gives > > > > > > > > > number error. I have manual change the row(4:4) in formula > or > > > > > > row(3:3) > > > > > > > > > anything to (1:1) in formula to get the corresponding > values of > > > > > > > > > another lookup. > > > > > > > > > > > Name Item > > > > > > > > > x 1 > > > > > > > > > y 2 > > > > > > > > > x 5 > > > > > > > > > y 8 > > > > > > > > > x 3 > > > > > > > > > > > OPF No CPO Item Qty Formula > > > > > > > > > x 1 > > > > > > > =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A11,ROW($A$1:$A$7)),ROW(1:1)), > > > > > > > > > 2) > > > > > > > > > x 5 > > > > > > > =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A11,ROW($A$1:$A$8)),ROW(2:2)), > > > > > > > > > 2) > > > > > > > > > x 3 > > > > > > > =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A11,ROW($A$1:$A$9)),ROW(3:3)), > > > > > > > > > 2) > > > > > > > > > y > > > > > > > =INDEX($A$1:$B$6,SMALL(IF($A$1:$A$6=A13,ROW($A$1:$A$6)),ROW(4:4)),2) > > > > > > > > > there I have to change the row(4:4) to row(1:1) to get the > > > > > > > > > corresponding value of the y lookup manual. > > > > > > > > > > > Regards > > > > > > > > > Bhushan Sabbani > > > > > > > > > 98208 26012 > > > > > > > > > > > -- > > > > > > ---------------------------------------------------------------------------------- > > > > > > > > > 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 athttp://www.excel-macros.blogspot.com > > > > > > > > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > > > > > > > > > 5. Excel Tips and Tricks athttp:// > 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 > > > > > > > > > > -- > > > > > > > > > > Correction in ur Formula.xls > > > > > > > > 19KViewDownload > > > > > > > > > > My Solution.xls > > > > > > > > 18KViewDownload- Hide quoted text - > > > > > > > > > > - Show quoted text - > > > > > > > > -- > > > > > > ---------------------------------------------------------------------------------- > > > > > > 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 athttp://www.excel-macros.blogspot.com > > > > > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > > > > > > 5. Excel Tips and Tricks athttp://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* > > > > > * * > > > > > *Ashish Koul* > > > > > *akoul*.*blogspot*.com <http://akoul.blogspot.com/> > > > > > *akoul*.wordpress.com <http://akoul.wordpress.com/> > > > > > My Linkedin Profile < > http://in.linkedin.com/pub/ashish-koul/10/400/830 > > > > > > > P Before printing, think about the environment.- Hide quoted text - > > > > > > > - Show quoted text -- Hide quoted text - > > > > > > - Show quoted text - > > > > > -- > > > > > > ---------------------------------------------------------------------------------- > > > 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 athttp://www.excel-macros.blogspot.com > > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > > > 5. Excel Tips and Tricks athttp://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 > > > > -- > > > > My Solution-1- For Gettting Text Result.xls > > 20KViewDownload- Hide quoted text - > > > > - Show quoted text - > > -- > > ---------------------------------------------------------------------------------- > 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 > -- -- ---------------------------------------------------------------------------------- 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
