Need a lookup to extract data table based on four criteria & override user input

I am working on a Quote Template for pricing out hoists.

1). The user will select from four separate dropdown lists: Reeving, Capacity, Model, & Trolley. Each dropdown list narrows the data for the next dropdown list. However, I want to have a "display table" that shows available hoists as the user is progressing through the dropdown lists. In other words, when the user has selected one ton capacity, he will see a table that shows all one ton hoists and related data those hoists. Then he can select a model and the "display table" will show data for only the selected capacity and model. And so on.... I have set up the dropdown lists but need help with the lookup for the display table. See requests and exampes on attached sheet.

2). I have a separate sheet that displays technical info and need to extract data from another worksheet. The challenge is that users need be able to override the default formula. However, when the user input is erased, the formulated value should show.

Details, requests, and examples are shown in the "Quote" and "Calculation" worksheets of the attached workbook.

Thanks in advance for your help!

David,

This looks absolutely fantastic! I was concerned that no one would want to take on something this difficult. Initially, I have a couple of questions and requests but will hold off on them untill I test this template. I will have the testing done by tomorrow so I will get back to you again shortly. Thanks again for all your hard work on this!

Marvin

David,

Once again, thank you so much for your help . You are the man! I cannot express how much I am impressed by your ability to put this together. After testing the quote template, I have some requests and questions. All NEW comments are highlighted in blue in the attached file.1). If I go through the Calculation sheet in order it works pretty well. However, if I have already selected the Reeving (C5), Capacity (C6), and Model (C7) but then change the Capacity again...the "Display Table" (G13:O13) is not updated. I have saved the the attached worksheet to show this and included new comments. I am actually using the price shown from the top row of the display table so a client could potentialy not realize that they hadn't changed the model number (after changing the Capacity) and the display table would show the wrong price. The Trolley selection (C8 on the Calculation sheet) works ok because if the user selects a trolley size or type that is not available with the selected Capacity and Model, because a zero is shown. In other words, if only one row is displayed in the "Display Table" then the data displayed needs to be blank (show error?) or match the criteria shown in all cells C5:C8. Hope this makes sense.

2). On the equipment net weight (quote tab) the lookup is only looking up the "base mount" weight, There are three trolley choices (each trolley type has a different weight) which are based on the user selection in cell C8 on the calculation sheet. You will notice on both "Spec" sheets that there is a "weight" column for "base mount", Monorail, and Double Girder. The Double Girder refers to all of the trolleys that are referenced by sizes such as 49 1/4, 55 1/8, etc...

3). Is there a way to highlight a cell if it has been overriden? If so, I would like for all cells that have been overriden to be highlighted until the formulated value is shown again. This way we can tell if something is has been changed by a user. Also, it would be nice if all of the cells that were overrided would go back to the formulated value after the quote template has been closed and re-opened.

4). Last but not least. The VBA change event does not seem to be working. I thought it was working initially but I am not sure if it is something I have done. The cell does not revert back to the formulated value. I changed a reference in a cell on the "Quote" tab so maybe that messed it up? I looked at the debugging option but am not familiar enough with VBA to figure how to get the code back to where it needs to be. I will be locking many of these cells before I send the template out to the client but it would be nice to understand how to get the VBA corrected in case I accidentally do it again.

Thanks again for your time and expertise!
Marvin

David,

Great job! This is going to work so well. The only thing I need to change is the colors of the cells (with the vba change event code) on the "Quote" tab. Our clients print this out in a pdf format so we would like for it to not be as noticeable. Like maybe a gray shade if it has been changed but nothing if it hasn't.

I do take your comments seriously and am trying to follow your steps and learn as we go. If you could explain how I can change the VBA (changing color of cells) without messing anything up I will go in and do it myself. I have copied and pasted VBA code before but haven't done any editing so I am not sure what the steps are for doing this. I am trying to learn as much as I can about VBA.

Since I am so new with VBA, I do have a couple of general questions. This workbook will be sent out to several different clients to use. I noticed when I open the workbook it asks me to enable macros. Is there a way that I and my clients can opt to enable macros for this workbook only? Kind of like the "trusted site" for Internet Explorer. Also, do I need to do anything to protect the code before I send the worksheet out to clients? Can the code be messed up from the sheet level?

Thanks for your patience and good work!
Marvin

David,

Thank you for explaining how to change the colors in VBA. You have been an invaluable resource and I am so grateful for your help and guidance. Also, I did go to the links below your name and there are some good resources there as well.

Marvin

David,

I have "added reputation" a couple of times and will continue to do so because I cannot give you enough kudos for your help on this project. Another question: I have managed to get a couple of errors in the VBA code at different stages which opens the "debugger". Is this occuring because I am trying to add rows or columns? In other words, does the VBA automatically update or does moving rows/columns mess up the VBA code? As in my instance, I may need to add a row on both the "Quote" sheet and the "Calculation" sheet. On the "Calculation" sheet the user will need to add rows quite often and it will be in between Rows 14 & 17 which is the same area that the "display table" is in. Will this cause any problems?

I know that I am going to have several questions in the future. Should I just post them to the forum or reply to your post? Not exactly sure what proper protocol is for this site.

Have a good day!
Marvin