Hi, I came across this formula which works well, however I would like to be able to apply it to G18:G150 not just to C1. Also I would like to change the formula, =A1, to {=dependency} this new formula should increment as it is copied down. Dependency = IF(C18="","",IF(G18="Task",MIN(IF(C19:C$87>C18,IF(C19:C$87 =1),VLOOKUP(E18,C$17:I$87,7,0)+F18,""))) Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(False, False) = "C1" Then If IsEmpty(.Value) Then Application.EnableEvents = False .Formula = "=A1" Application.EnableEvents = True End If End If End With End Sub Thanks in advance.

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Overwriting a formula in a range with a value, but still keep the formula if value is deleted

Thread starter pivers
Start date Dec 4, 2018
Tags

=a1 end false formula target

pivers

New Member

Dec 4, 2018

Hi,

I came across this formula which works well, however I would like to be able to apply it to G18:G150 not just to C1. Also I would like to change the formula, =A1, to {=dependency} this new formula should increment as it is copied down.

Dependency = IF(C18="","",IF(G18="Task",MIN(IF(C19:C$87>C18,IF(C19:C$87<c18+1,h19:h$87))),if(and(g18="activity",e18>=1),VLOOKUP(E18,C$17:I$87,7,0)+F18,"")))

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

With Target

If .Address(False, False) = "C1" Then

If IsEmpty(.Value) Then

Application.EnableEvents = False

.Formula = "=A1"

Application.EnableEvents = True

End If

End If

End With

End Sub

Thanks in advance.</c18+1,h19:h$87))),if(and(g18="activity",e18>