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

Marvelous,

This one was not easy - it took me half a day. Normally I crack posts on here in less than 30 mins!

On the calculation sheet I have put in a series of formulas that pulls the correct data based on the drop down selections. This is done using array formulas, finding which rows are appropriate for the relevant criteria and then pulling the data.

What you will find is that the final selection is at the top of the table. This makes pulling the data through onto the quote tab easy. I do not understand what data you want on the trolley speeds field so I have coloured this red.

For the rope diameter and equipment net weigh this is just a vlookup on the correct sheet.

To allow the users to override the data on the quote tab, and then have the formulas pop back if that data is deleted I have put some code in the worksheet change event. So everytime something changes on the quote sheet it checks whether any of the relevant cells are blank. If they are the formulas are put back in.

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("E18").Value = "" Then Range("E18").Formula = "=Calculation!$H$13"
If Range("E19").Value = "" Then Range("E19").Formula = "=Calculation!$J$13"
If Range("E20").Value = "" Then Range("E20").Formula = "=Calculation!$I$13"
If Range("E22").Value = "" Then Range("E22").Formula = "=Calculation!$K$13"
If Range("G22").Value = "" Then Range("G22").Formula = "=Calculation!$M$13"
If Range("E27").Value = "" Then Range("E27").Formula = "=IF(Calculation!C5=""SingleReeved"",VLOOKUP(Quote!E16,'Specs SR 1-90t'!E15:F293,2,FALSE),VLOOKUP(E16,'Specs DR 1-70t'!E15:F128,2,FALSE))"
If Range("E28").Value = "" Then Range("E28").Formula = "=IF(Calculation!C5=""SingleReeved"",VLOOKUP(E16,'Specs SR 1-90t'!E15:G293,3,FALSE),VLOOKUP(E16,'Specs DR 1-70t'!E15:G128,3,FALSE))"

End Sub

Take a look and come back with questions.

You need to do formal testing to make sure you have what you want before you use with clients - the risk here is yours!

Regards

David