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

Marvin,

I have made the amendments, see attached.

1) to ensure that noting is displayed if they choose a model and then go back and change the weight I have amended the formula so you in effect get and AND for model and capacity - if these to are not in line then nothing is displayed

=IFERROR(SMALL(IF($C$5="SingleReeved",IF($C$7<>"",IF(($C$7=Model)*($E$6=Capacity),ROW(Model)),IF($C$6="",ROW(Capacity),IF($E$6=Capacity,ROW(Capacity)))),IF($C$7<>"",IF(($C$7=ModelDR)*($E$6=CapacityDR),ROW(ModelDR)),IF($C$6="",ROW(CapacityDR),IF($E$6=CapacityDR,ROW(CapacityDR))))),ROW(G1)),"")

2) the formula for the weigh and trolley speed now pulls based on whether Base Mount monorail or other is selected - this is achieved through nested if and vlookups. Check the rope diameter formula as well as I think I changed for some reason

3) Where cell formulas are overridden the cell then displays in orange viathe Quote worksheet change event, code is below. If the formulas are not as they should be the cells are orange, if they are reset they are coloured green

Private Sub Worksheet_Change(ByVal Target As Range)

'if cells are empty reset to default formulas
If Range("E18").Value = "" Then
    Range("E18").Formula = "=Calculation!$H$13"
    Range("E18").Interior.Color = RGB(0, 176, 80)
End If
If Range("E19").Value = "" Then
    Range("E19").Formula = "=Calculation!$J$13"
    Range("E19").Interior.Color = RGB(0, 176, 80)
End If
If Range("E20").Value = "" Then
    Range("E20").Formula = "=Calculation!$I$13"
    Range("E20").Interior.Color = RGB(0, 176, 80)
End If
If Range("E22").Value = "" Then
    Range("E22").Formula = "=Calculation!$K$13"
    Range("E22").Interior.Color = RGB(0, 176, 80)
End If
If Range("G22").Value = "" Then
    Range("G22").Formula = "=Calculation!$M$13"
    Range("G22").Interior.Color = RGB(0, 176, 80)
End If
If Range("G23").Value = "" Then
    Range("G23").Formula = "=IF(Calculation!$C$5=""SingleReeved"",IF(Calculation!$C$8=""Base Mount"","""",IF(Calculation!$C$8=""Monorail"",VLOOKUP(Calculation!$C$7,'Specs SR 1-90t'!$E$15:$K$293,6,FALSE),VLOOKUP(Calculation!$C$7,'Specs SR 1-90t'!$E$15:$K$293,8,FALSE))),IF(Calculation!$C$8=""Base Mount"","""",IF(Calculation!$C$8=""Monorail"",VLOOKUP(Calculation!$C$7,'Specs DR 1-70t'!$E$15:$K$293,6,FALSE),VLOOKUP(Calculation!$C$7,'Specs DR 1-70t'!$E$15:$K$293,8,FALSE))))"
    Range("G23").Interior.Color = RGB(0, 176, 80)
End If
If Range("E27").Value = "" Then
    Range("E27").Formula = "=IF(Calculation!$C$5=""SingleReeved"",VLOOKUP(Calculation!$L$13,'Specs SR 1-90t'!$E$15:$F$293,2,FALSE),VLOOKUP(Calculation!$L$13,'Specs DR 1-70t'!$E$15:$F$128,2,FALSE))"
    Range("E27").Interior.Color = RGB(0, 176, 80)
End If
If Range("E28").Value = "" Then
    Range("E28").Formula = "=IF(Calculation!$C$5=""SingleReeved"",IF(Calculation!$C$8=""Base Mount"",VLOOKUP(Calculation!$C$7,'Specs SR 1-90t'!$E$15:$K$293,3,FALSE),IF(Calculation!$C$8=""Monorail"",VLOOKUP(Calculation!$C$7,'Specs SR 1-90t'!$E$15:$K$293,5,FALSE),VLOOKUP(Calculation!$C$7,'Specs SR 1-90t'!$E$15:$K$293,7,FALSE))),IF(Calculation!$C$8=""Base Mount"",VLOOKUP(Calculation!$C$7,'Specs DR 1-70t'!$E$15:$K$293,3,FALSE),IF(Calculation!$C$8=""Monorail"",VLOOKUP(Calculation!$C$7,'Specs DR 1-70t'!$E$15:$K$293,5,FALSE),VLOOKUP(Calculation!$C$7,'Specs DR 1-70t'!$E$15:$K$293,7,FALSE))))"
    Range("E28").Interior.Color = RGB(0, 176, 80)
End If

'if formulas in cells are empty - i.e. overridden - highlight cells in orange
If Range("E18").Formula <> "=Calculation!$H$13" Then Range("E18").Interior.Color = RGB(255, 153, 51)
If Range("E19").Formula <> "=Calculation!$J$13" Then Range("E19").Interior.Color = RGB(255, 153, 51)
If Range("E20").Formula <> "=Calculation!$I$13" Then Range("E20").Interior.Color = RGB(255, 153, 51)
If Range("E22").Formula <> "=Calculation!$K$13" Then Range("E22").Interior.Color = RGB(255, 153, 51)
If Range("G22").Formula <> "=Calculation!$M$13" Then Range("G22").Interior.Color = RGB(255, 153, 51)
If Range("G23").Formula <> "=IF(Calculation!$C$5=""SingleReeved"",IF(Calculation!$C$8=""Base Mount"","""",IF(Calculation!$C$8=""Monorail"",VLOOKUP(Calculation!$C$7,'Specs SR 1-90t'!$E$15:$K$293,6,FALSE),VLOOKUP(Calculation!$C$7,'Specs SR 1-90t'!$E$15:$K$293,8,FALSE))),IF(Calculation!$C$8=""Base Mount"","""",IF(Calculation!$C$8=""Monorail"",VLOOKUP(Calculation!$C$7,'Specs DR 1-70t'!$E$15:$K$293,6,FALSE),VLOOKUP(Calculation!$C$7,'Specs DR 1-70t'!$E$15:$K$293,8,FALSE))))" Then Range("G23").Interior.Color = RGB(255, 153, 51)
If Range("E27").Formula <> "=IF(Calculation!$C$5=""SingleReeved"",VLOOKUP(Calculation!$L$13,'Specs SR 1-90t'!$E$15:$F$293,2,FALSE),VLOOKUP(Calculation!$L$13,'Specs DR 1-70t'!$E$15:$F$128,2,FALSE))" Then Range("E27").Interior.Color = RGB(255, 153, 51)
If Range("E28").Formula <> "=IF(Calculation!$C$5=""SingleReeved"",IF(Calculation!$C$8=""Base Mount"",VLOOKUP(Calculation!$C$7,'Specs SR 1-90t'!$E$15:$K$293,3,FALSE),IF(Calculation!$C$8=""Monorail"",VLOOKUP(Calculation!$C$7,'Specs SR 1-90t'!$E$15:$K$293,5,FALSE),VLOOKUP(Calculation!$C$7,'Specs SR 1-90t'!$E$15:$K$293,7,FALSE))),IF(Calculation!$C$8=""Base Mount"",VLOOKUP(Calculation!$C$7,'Specs DR 1-70t'!$E$15:$K$293,3,FALSE),IF(Calculation!$C$8=""Monorail"",VLOOKUP(Calculation!$C$7,'Specs DR 1-70t'!$E$15:$K$293,5,FALSE),VLOOKUP(Calculation!$C$7,'Specs DR 1-70t'!$E$15:$K$293,7,FALSE))))" Then Range("E28").Interior.Color = RGB(255, 153, 51)

End Sub

I have put some code in the worksheet open event macro to reset the formulas when the workbook is opened, see below

Private Sub Workbook_Open()

Worksheets("Quote").Range("E18").Formula = "=Calculation!$H$13"
Worksheets("Quote").Range("E18").Interior.Color = RGB(0, 176, 80)
Worksheets("Quote").Range("E19").Formula = "=Calculation!$J$13"
Worksheets("Quote").Range("E19").Interior.Color = RGB(0, 176, 80)
Worksheets("Quote").Range("E20").Formula = "=Calculation!$I$13"
Worksheets("Quote").Range("E20").Interior.Color = RGB(0, 176, 80)
Worksheets("Quote").Range("E22").Formula = "=Calculation!$K$13"
Worksheets("Quote").Range("E22").Interior.Color = RGB(0, 176, 80)
Worksheets("Quote").Range("G22").Formula = "=Calculation!$M$13"
Worksheets("Quote").Range("G22").Interior.Color = RGB(0, 176, 80)
Worksheets("Quote").Range("G23").Formula = "=IF(Calculation!$C$5=""SingleReeved"",IF(Calculation!$C$8=""Base Mount"","""",IF(Calculation!$C$8=""Monorail"",VLOOKUP(Calculation!$C$7,'Specs SR 1-90t'!$E$15:$K$293,6,FALSE),VLOOKUP(Calculation!$C$7,'Specs SR 1-90t'!$E$15:$K$293,8,FALSE))),IF(Calculation!$C$8=""Base Mount"","""",IF(Calculation!$C$8=""Monorail"",VLOOKUP(Calculation!$C$7,'Specs DR 1-70t'!$E$15:$K$293,6,FALSE),VLOOKUP(Calculation!$C$7,'Specs DR 1-70t'!$E$15:$K$293,8,FALSE))))"
Worksheets("Quote").Range("G23").Interior.Color = RGB(0, 176, 80)
Worksheets("Quote").Range("E27").Formula = "=IF(Calculation!$C$5=""SingleReeved"",VLOOKUP(Calculation!$L$13,'Specs SR 1-90t'!$E$15:$F$293,2,FALSE),VLOOKUP(Calculation!$L$13,'Specs DR 1-70t'!$E$15:$F$128,2,FALSE))"
Worksheets("Quote").Range("E27").Interior.Color = RGB(0, 176, 80)
Worksheets("Quote").Range("E28").Formula = "=IF(Calculation!$C$5=""SingleReeved"",IF(Calculation!$C$8=""Base Mount"",VLOOKUP(Calculation!$C$7,'Specs SR 1-90t'!$E$15:$K$293,3,FALSE),IF(Calculation!$C$8=""Monorail"",VLOOKUP(Calculation!$C$7,'Specs SR 1-90t'!$E$15:$K$293,5,FALSE),VLOOKUP(Calculation!$C$7,'Specs SR 1-90t'!$E$15:$K$293,7,FALSE))),IF(Calculation!$C$8=""Base Mount"",VLOOKUP(Calculation!$C$7,'Specs DR 1-70t'!$E$15:$K$293,3,FALSE),IF(Calculation!$C$8=""Monorail"",VLOOKUP(Calculation!$C$7,'Specs DR 1-70t'!$E$15:$K$293,5,FALSE),VLOOKUP(Calculation!$C$7,'Specs DR 1-70t'!$E$15:$K$293,7,FALSE))))"
Worksheets("Quote").Range("E28").Interior.Color = RGB(0, 176, 80)

End Sub

4) Please retest changes to the Quote worksheet. The changes seem to work for me.

I have enjoyed working your problem but remember the purpose of forum is to teach concepts and share knowledge rather than get a particular spreadsheet right. If you need that kind of help then we need to operate a different model!

Regards

David