Dear all, =OFFSET(AE1,MATCH(B8,AE2:AE65536,0),1,COUNTIF(AE2:AE65536,B8),1) The offset function given by Vinay is working absolutely fine, but once the Product changes, Specification should be refreshed.
Can this problem be solved using macros. I am good in excel, but very bad in Macros. I am trying to use worksheet_selectionchange in the macros, but not able to write the code Regards, Sandeep. On Tue, Apr 14, 2009 at 9:18 PM, VJ IR <vji...@gmail.com> wrote: > Dear All, > > In the validation column try using a combination of these formulaes: > > > =OFFSET(AE1,MATCH(B8,AE2:AE65536,0),1,COUNTIF(AE2:AE65536,B8),1) > Wherein AE2 : AE65536 should contain thelist of products chosen.and given > the specifications alongside these in the next column. As a result of the > Offset formulae only the specifications matching the product shall be > displayed. > > I hope this resolves your query. > > Regards, > Vijay Iyer > > > On Tue, Apr 14, 2009 at 12:16 PM, Prashant Pednekar <prashant...@gmail.com > > wrote: > >> Hi All >> >> here my doubt is >> The Validation is not having constraint, >> i.e. afte entering one product and specification, if i am again changing >> the product.the specifition remain same? >> >> Eg. >> Product 2 Specification 6 >> >> how can we resolve this ? >> >> Regards. >> Prashant. >> On Wed, Apr 8, 2009 at 11:33 PM, Harmeet Singh >> <harmeet.hew...@gmail.com>wrote: >> >>> Plz find attached file. >>> >>> >>> >>> >>> On Wed, Apr 8, 2009 at 4:10 PM, SANDEEP <sandeepkunc...@gmail.com>wrote: >>> >>>> >>>> Dear All, >>>> I require a macro for Cell Validation and drop down list - for 2nd >>>> column as described below >>>> >>>> 1st column contains the list of Items(Product Names) for which i have >>>> given a Validation and cell drop down >>>> 2nd column - I want the validation and cell drop down to contain only >>>> the specification for the product selected in the First Column. For >>>> example >>>> if i select the product Bottle Oval in 1st column - in the second column >>>> drop down i should get only the specification available for Bottle Oval >>>> such >>>> as 5 ml; 10 ml; 50 ml. >>>> >>>> the list of Product and Specification master available is as follows >>>> >>>> Product 1 >>>> >>>> Specification 1 >>>> >>>> Product 1 >>>> >>>> Specification 4 >>>> >>>> Product 1 >>>> >>>> Specification 6 >>>> >>>> Product 1 >>>> >>>> Specification 10 >>>> >>>> Product 2 >>>> >>>> Specification 4 >>>> >>>> Product 2 >>>> >>>> Specification 10 >>>> >>>> Product 3 >>>> >>>> Specification 1 >>>> >>>> Product 3 >>>> >>>> Specification 2 >>>> >>>> Product 3 >>>> >>>> Specification 3 >>>> >>>> Product 3 >>>> >>>> Specification 4 >>>> >>>> Product 3 >>>> >>>> Specification 5 >>>> >>>> Product 3 >>>> >>>> Specification 6 >>>> >>>> Regards, >>>> Sandeep >>>> >>>> >>>> >>>> >>> >>> >>> -- >>> Thanks & Regards, >>> >>> Harmeet Singh >>> >>> >>> >> >> >> > > > > --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
sandy.xls
Description: MS-Excel spreadsheet
