Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2007
    Location
    Devon, UK
    Posts
    37

    Unanswered: Narrowing Search

    Basically our company has just over 1300 products. This sheet im creating, we have to type a product code into a cell and have it produce a load of information for that product code.

    product codes are all in the format

    116avt3f4285

    they all start with 3 numbers.

    What i was hoping was possible, was that by typing only the first 3 letters of the product code into the cell, a list is automatically produced that would only contain all of the product codes that start with those 3 numbers. The other option i can think of is that i could have a button that would open a dialog box, in which i could enter the 1st 3 digits and have that update a cell to have some kind of list for those products only.

    I know it sounds odd, but as im not really an expert on excel or VB for excell, i was kind of hoping somebody would be able to come up with a full solution for me, failing that, a huge point in the right direction would be great.

    thanks for reading

    Lf

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Why not have the list of products on another sheet in the workbook, and then limit the key cell to that list? It appears as a drop-down box, and the users can then scroll to the correct product code.

  3. #3
    Join Date
    Feb 2007
    Location
    Devon, UK
    Posts
    37
    hello weejas,

    i like the idea, i currently have the products set up on a seperate sheet in the same workbook, i have used the cell i want the product code to appear in and set up as a list. What i need now, if poosible, say i was to type the 1st 3 numbers of the product code into the cell, i could then click the dropdown list and only get the options being the product codes that started with those 1st 3 letters, currently it brings up an error saying that the value i enter is not valid and that the user has restricted the values that can be entered into the cell
    Last edited by lovinfeelin; 08-22-08 at 05:37.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I know of no way to do this in native Excel, although a with little time I could probably come up with something based on a userform or an ActiveX control.
    It seems to me that what you're trying to achieve would be better done in Access, as that has better querying capacity. The point of the dropdown list is that the users don't have to type anything at all - they just scroll down and select the required product code.
    Just out of interest, why do you want the users to type in the first three digits of the product code, and then select the full code from a cut-down list?

  5. #5
    Join Date
    Feb 2007
    Location
    Devon, UK
    Posts
    37
    Id like to point out that this isnt my spreadsheet, i am doing this for my boss who wanted it set out this way. Hence why access wont be usable. The product lists are automatically pulled in from an in house system and can only be read into excel currently.

    Why i need to narrow the list down stems from two reasons, 1st, we have over 1300 products, that in itself should enough of a reason to want to narrow it down in any way possible, 2nd, the people who are going to be using the actual sheet in the end have a low computer literacy, it will be the production staff for a factory, so anything that can better help them product more accurate results is what we need. Hence why my boss had this in the specification. So if you could get the chance to sort something out for me i would be highly appreciativem although if you do think that it would take more than 1 week to do it wouldnt be worth your time starting, as i am moving to another department in the company after that.

    Once again thankyou for replying though

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    If the list of product codes is static and sorted alphabetically, you could possibly do something with a pair of ActiveX combo box controls. You'd need two reference lists - one of the initial three character prefixes of the product codes as well.
    You'd need to make a list of the ranges in the product code list that relate to each prefix, and use that list in a custom function:
    Code:
    Function CodeRange(strPrefix As String) As String
       'Function to convert a product prefix into a range
    
       Select Case strPrefix
    
          Case "000"
    
             CodeRange = "A2:H95"
    
          Case "001"
    
             CodeRange = "A96:H268"
    
          Case "002"
    
             CodeRange = "A269:H572"
    
       End Select
    
    Exit Function
    Obviously, you'd need as many Case statements as there are prefixes.

    You'd also need to code the combo boxes. Call the first one cmbPrefix and the other one cmbProduct. Set the column count property of both of them to 1. Set the row source property of cmdPrefix to the name of the prefix range. On the Change event of cmbPrefix, use code approximating to the following:
    Code:
    cmbProduct.RowSource = "SheetName!" & CodeRange(cmbPrefix)
    This will set the row source property of cmbProduct to the range containing the products that start with the prefix in cmbPrefix. You can then write code for the Change event of cmbProduct to populate other cells with product information.
    The only downside is that it's not very easy to amend - if the product list changes, the function needs to be re-written to accommodate this.

    Hope this makes sense - if you like, I'll bang an example together.

  7. #7
    Join Date
    Feb 2007
    Location
    Devon, UK
    Posts
    37
    Sorry for the delay in my reply, had a couple of days off of work in between. If you could make up an example for me that would be great, i could attach a copy of the file i am working on at some point this evening if that would be easier for you, would also help to better explain what i need, unfotunatly i dont get any zipping software here at work, so will have to wait until i am home to do so.

    thanks again

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Here you go!

    I mocked up some data, but the principle is there. As I stated earlier, it's more than a little clunky, but you mentioned that you won't be there for too much longer, so it won't be your problem!
    Attached Files Attached Files

  9. #9
    Join Date
    Feb 2007
    Location
    Devon, UK
    Posts
    37
    Thank you very much mate, that is pretty much exactly what she wanted, and yeah like you say, im not going to have to worry about how clunky it is, lsat day of this job tomorrow

    once again, thank you for all of your help.

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2

    Wink

    Glad to help, and thanks for letting me know that it worked!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •