Hello everyone. I have been working at this issue for a while. I currently have a workorder book for my small business that I use for billing. It is made up with a sheet titled ‘workorder’ and a second sheet titled ‘materials’. The focus of my question has to do specifically with the material section of my ‘workorder’. This section is divided into four columns: Item, Serial Number, Quantity, Price. Item column is a validity list from the ‘materials’ sheet, allowing me to select from the list of materials I use (about 1200 entries). Serial Number is broken and is what I am trying to solve. Quantity is obvious for the quantity of materials used. Price has the formula
=IF(ISNA(VLOOKUP(A21,$'Material Stock'.A$3:E$1197,5,0)),"",VLOOKUP(A21,$'Material Stock'.A$3:E$1197,5,0)*F21)
This allows me to select an item from the drop down menu, add a quantity and a price will auto fill into the cell. My problem is that I have such a long list of materials at this point, it is miserable trying to scroll through all of them to find the one I want. My goal is to have both the ability to use the drop down menu or input a serial number and have the cells in the row fill out (material name and price based on qty). My dilemma is that when using validity in a cell, you are not able to add any formulas to it. I am thinking I will need to find a macro that will auto-choose from the drop down based on input from the Serial Number cell.