none
[Excel] Dynamic List RRS feed

  • Question

  • I want to manage my inventory consisting of thousands of products and was thinking of creating an inventory sheet that the person would fill in for inwards and outwards. I tried creating a list and then using data validation on cells in a column to only allow entry in a standardized name format. However, since it consists of 1000's of items, scrolling is tiresome, and so i was thinking if it was possible to have a dynamic list, that when i enter key words should only show items that contain the entered word in that list. Eg:

    I have a "Coarse thread Gypsum of size 3/4 x 8" that has been moved from the inventory, i'd like to combine key combinations like "Coarse" or "Gypsum" and the list should only show those inventory items that contain these key words. What would be a approach that i could take ??

    Thursday, March 12, 2015 7:01 PM

Answers

All replies

  • I made this file for a friends years ago, but it's not made for a very large Inventory list.

    Have a look at it and see if anything in it can help you.

    http://dropcanvas.com/4r9q7


    Cimjet



    • Edited by Cimjet Saturday, March 14, 2015 2:19 PM
    Thursday, March 12, 2015 8:14 PM
  • Thanks a lot for replying. I'm currently analysing your file as there are really good things i can implement into the file i'm planning to make, so gimme some time and i'll comeback to this with my working file which would probably contain a ton of goofups, which you'd probably help me with. Thanks again
    Saturday, March 14, 2015 6:40 PM
  • In the Module 1 Find this macro.

    Sub InventoryFind()

    Dim PartFind As String, rngFind As Range, qty As Long
    PartFind = InputBox("Type Part Number")
    If PartFind = "" Then Exit Sub
    With Sheets("Stock").Columns(1)
    Set rngFind = .Find(What:=PartFind, LookIn:=xlValues, LookAt:=xlWhole)
    If rngFind Is Nothing Then
    MsgBox "Value could not be found."
    Exit Sub
    End If
    Application.Goto rngFind, True
    End With

    End Sub

    Change the last word in this line:

    Set rngFind = .Find(What:=PartFind, LookIn:=xlValues, LookAt:=xlPart)

    Change "xlWhole" to "xlPart"

    This way you don't need to type an asterisk (*) to do your find.

    Place a letter in the middle of the set of numbers then do a "Find with just that letter.

    You'll need to play with it.

    P.S. Don't change that word elsewhere.


    Cimjet


    • Edited by Cimjet Saturday, March 14, 2015 7:39 PM
    Saturday, March 14, 2015 7:38 PM
  • I’ve got an other file I made later for the same friend but he didn’t like it. Too many buttons and found it complicated. If you're interested, I can post the link to the file.


    Cimjet

    Saturday, March 14, 2015 8:28 PM
  • Sure do. I can experiment much more if i have more codes to see and understand.
    Sunday, March 15, 2015 2:07 PM
  • Here it is:

    http://dropcanvas.com/sbwn4


    Cimjet

    Sunday, March 15, 2015 3:07 PM
  • Ok. I came up with the stock file that i want. Its a lot different, which i think will do for me, but do suggest improvements. What i want is that i have applies data validation list in "Working" sheet column C & E. Since there is a long list of items that will get added to it, i want it so that when the person enters just a few keywords, the data need to only appear that contain similar keywords. Is it somehow possible ??

    Here's the file:

    http://dropcanvas.com/a473h

    Tuesday, March 17, 2015 9:51 AM
  • I can't change the way Data Validation works and with thousand of cells, we can't use Combobox that we can program.

    You can index your parts list like below, so you can type D and it will take you to that letter then you just scroll the D section. You can index any section you like.

    B
    BISONKIT
    BLACK DRY WALL SCREWS1 X 6
    BLACK DRY WALL SCREWS1 X 8
    C
    C.I.FIX AVO ART 105A3"
    C.I.FIX AVO ART 105A4"
    D
    DOUBLE BOLT SWEWAL50MM
    DOUBLE PIN BRAKE50MM

    Cimjet

    Tuesday, March 17, 2015 11:13 PM
  • yes, but is it not possible to, for eg: have the list dynamically refresh, either through query or other means as the main trouble i'm having in enforcing this plan is people do not want to scroll through 1000 items from the list. And without a standard format, they duplicate items by entering something in a different format. I want to use this list to also implement it in other areas as well
    Wednesday, March 18, 2015 6:14 AM
  • Maybe Ms.Access would be a better choice but that is out of my confort zone.

    I can't do what you want and don't know if it can be done.

    The algorithm needed if it could be done is beyond me.

    Sorry

    John


    Cimjet

    Wednesday, March 18, 2015 1:06 PM
  • I would post again and try other forum.

    This one is for VBA programs.

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba

    This one is Excel in general but I know a few guys that are very good in programming.

    http://answers.microsoft.com/en-us/office/forum/excel?tab=Threads

    With your Username and password, you should be able to post in those forum.


    Cimjet


    • Edited by Cimjet Saturday, March 28, 2015 1:11 PM
    • Marked as answer by huzzug Tuesday, January 19, 2016 4:03 PM
    Wednesday, March 18, 2015 1:59 PM