I am building a quoting spreadsheet in Microsoft Excel. I want to set up a sheet where our sales people will pick a product class. This will then modify a drop down list to only show the item descriptions that have that product class. After they select the item I want the units of usage and item part number to automatically fill in on the sheet. This needs to either be done in one set of master cells and then be copied into the correct portion of the sheet on the next available line or be stacked in a column and individual from each time the action was completed above or below it. Filling this part out on a master portion of the sheet will require a copy macro to automatically move the information. That will avoid user error on copying the information.
The master item list has been imported into a separate sheet from an MSSQL database. I then created a pivot table on another sheet to allow me to see the sorted data by the product class. If I create a named range to use for the list I can get a drop down list that will change as I change the pivot table. I don't know if it is possible to have a drop down list on the quote sheet change the selection of the product class in the pivot table. So far I have not figured out how to do that. I want to try to avoid running a VB macro for each line as that will probably slow down the process enough that building this sheet will not speed up the process.
I need to auto fill the units of measure and part number since nobody remembers them based on the part description. The selection of the item could be done by the part number only if I can also display the description since most people wonít remember a part by the number.
I just donít feel that I am making good headway on this project. If any of you have any ideas about how you would do this I would love to hear them. I realize this is not security related but I am hoping one of you has done something similar to this before.