I am fairly well versed in excel, with the exception of complicated tasks (pivot tables, vba, etc.).
My employer is asking if I can make an inventory connected to the Bill of Materials spreadsheet. What this would entail is a drop-down for each inventory item I am inserting into the BOM. This is simple, but I would like to know if there is a way to have a separate combobox used to sort the list of inventory items by columns. It would be able to sort by description, part number, series, cost, or any of the other column titles. And with this pre-emptive sort, I would like to browse the hundreds-thousands of parts with the remaining combo boxes for the part we are looking for, select it, and it will automatically fill the remaining fields of important information (description and cost).

I do believe that this would be most useful if there is a separate 'Master' excel file, with the inventory, contacts, and what-not. This would have to be linked to the basic blank BOM form.

Any help will be much appreciated.