[lovinfeelin]

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

[weejas]

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.

[lovinfeelin]

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

[weejas]

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?

[lovinfeelin]

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

[weejas]

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:

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:

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.

[lovinfeelin]

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

[weejas]

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!

[lovinfeelin]

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.

[weejas]

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