title: Direct input or Formula
body: <p>I have a spreadsheet where I would like the end user to be able to enter a value OR the spreadsheet will automatically populate a formula. I would also like it to re-populate the formula if the direct input is deleted. Currently the below code does nothing. Any help would be greatly appreciated!</p>
  
  <pre><code>Private Sub Worksheet_Change1(ByVal target As Range)
  
  If target.Cells.Count &gt; 1 Then Exit Sub
  
  If Not Intersect(target, Range("B8")) Is Nothing Then
  
  Range("B8").Formula = "=IF($B$1=""DC"",IF(MAX('TY Data'!$L:$L)&gt;=VLOOKUP('Wage Run'!$F$1,Dates!$A:$D,3,0),SUMIFS('TY Data'!$K:$K,'TY Data'!$A:$A,5101200,'TY Data'!$L:$L,VLOOKUP('Wage Run'!$F$1,Dates!$A:$D,3,0),'TY Data'!$D:$D,""AP0345R"",'TY Data'!$I:$I,'Wage Run'!$D$1),0),IF($B$1=""Division"",IF(MAX('TY Data'!$L:$L)&gt;=VLOOKUP('Wage Run'!$F$1,Dates!$A:$D,3,0),SUMIFS('TY Data'!$K:$K,'TY Data'!$A:$A,5101200,'TY Data'!$L:$L,VLOOKUP('Wage Run'!$F$1,Dates!$A:$D,3,0),'TY Data'!$D:$D,""AP0345R"",'TY Data'!$M:$M,'Wage Run'!$D$1),0)" _
  &amp; ",IF($B$1=""GBU"",IF(MAX('TY Data'!$L:$L)&amp;&gt;=VLOOKUP('Wage Run'!$F$1,Dates!$A:$D,3,0),SUMIFS('TY Data'!$K:$K,'TY Data'!$A:$A,5101200,'TY Data'!$L:$L,VLOOKUP('Wage Run'!$F$1,Dates!$A:$D,3,0),'TY Data'!$D:$D,""AP0345R"",'TY Data'!$N:$N,'Wage Run'!$D$1),0),IF($B$1=""Rollup"",IF(MAX('TY Data'!$L:$L)&gt;=VLOOKUP('Wage Run'!$F$1,Dates!$A:$D,3,0),SUMIFS('TY Data'!$K:$K,'TY Data'!$A:$A,5101200,'TY Data'!$L:$L,VLOOKUP('Wage Run'!$F$1,Dates!$A:$D,3,0),'TY Data'!$D:$D,""AP0345R"",'TY Data'!$O:$O,'Wage Run'!$D$1),0),""""))))"
  
  End If
  End Sub
  </code></pre>
  
  <p>Edit:</p>
  
  <p>New formula looks like this after I added it to the old sub</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal target As Range)
  
  If target.Cells.Count &gt; 1 Then Exit Sub
  
  If Not Intersect(target, Range("B1")) Is Nothing Then
      Range("C1").ClearContents
  End If
  
  If target.Cells.Count &gt; 1 Then Exit Sub
  
  If Not Intersect(target, Range("B8")) Is Nothing And target.Value = "" Then
  
  Range("B8").Formula = "=IF($B$1=""DC"",IF(MAX('TY Data'!$L:$L)&gt;=VLOOKUP('Wage Run'!$F$1,Dates!$A:$D,3,0),SUMIFS('TY Data'!$K:$K,'TY Data'!$A:$A,5101200,'TY Data'!$L:$L,VLOOKUP('Wage Run'!$F$1,Dates!$A:$D,3,0),'TY Data'!$D:$D,""AP0345R"",'TY Data'!$I:$I,'Wage Run'!$D$1),0),IF($B$1=""Division"",IF(MAX('TY Data'!$L:$L)&gt;=VLOOKUP('Wage Run'!$F$1,Dates!$A:$D,3,0),SUMIFS('TY Data'!$K:$K,'TY Data'!$A:$A,5101200,'TY Data'!$L:$L,VLOOKUP('Wage Run'!$F$1,Dates!$A:$D,3,0),'TY Data'!$D:$D,""AP0345R"",'TY Data'!$M:$M,'Wage Run'!$D$1),0)" _
  &amp; ",IF($B$1=""GBU"",IF(MAX('TY Data'!$L:$L)&amp;&gt;=VLOOKUP('Wage Run'!$F$1,Dates!$A:$D,3,0),SUMIFS('TY Data'!$K:$K,'TY Data'!$A:$A,5101200,'TY Data'!$L:$L,VLOOKUP('Wage Run'!$F$1,Dates!$A:$D,3,0),'TY Data'!$D:$D,""AP0345R"",'TY Data'!$N:$N,'Wage Run'!$D$1),0),IF($B$1=""Rollup"",IF(MAX('TY Data'!$L:$L)&gt;=VLOOKUP('Wage Run'!$F$1,Dates!$A:$D,3,0),SUMIFS('TY Data'!$K:$K,'TY Data'!$A:$A,5101200,'TY Data'!$L:$L,VLOOKUP('Wage Run'!$F$1,Dates!$A:$D,3,0),'TY Data'!$D:$D,""AP0345R"",'TY Data'!$O:$O,'Wage Run'!$D$1),0),""""))))"
  
  End If
  
  End Sub
  </code></pre>