Thanks Richard...however, it's not working. It seems to be something with the VLOOKUP formulas.
I have attached 2 pics - one that shows the formula is not correct in the cell, and the other that there is no formula in the cell.
Here is the code I'm using...
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Application.EnableEvents = False
Target.Cells(1, 2) = "=IF(B" & Target.Row & "="""","""",B" & Target.Row & "-DATE(YEAR(B" & Target.Row & "),1,1)+1)"
Application.EnableEvents = True
End If
If Not Intersect(Target, Range("F:F")) Is Nothing Then
Application.EnableEvents = False
Target.Cells(1, 2) = "=IF(F" & Target.Row & "="""","""",VLOOKUP(F" & Target.Row & ",'Vend_Prod (2)'!Vend_Prod,2,FALSE))"
Application.EnableEvents = True
End If
If Not Intersect(Target, Range("G:G")) Is Nothing Then
Application.EnableEvents = False
Target.Cells(1, 2) = "=IF(G" & Target.Row & "="""","""",VLOOKUP(G" & Target.Row & ",'Vend_Prod (2)'!D:E,2,FALSE))"
Application.EnableEvents = True
End If
Worksheets("Pivot for Avg Use").PivotTables("PivotTable3").PivotCache.Refresh
End Sub
Bookmarks