Copy Formula Down On Cell Entry

[goss]

Hi all,

Using mix of Excel 2003/2010.

I would like to perform a simple check for duplication

I have a dynamic name range setup that will expand as my list of invoices grows
lstInvoices = Data!$A$2:INDEX(Data!$A:$A,COUNTA(Data!$A:$A))

And I have a formula to check for duplicates
=IF(COUNTIF(lstInvoices,A2)>1,"Duplicate","") <-Formula to copy down

I would like to copy the formula down from B2 to B3 when a user make an entry in A3 and so forth as additional orders are entered in Col A.

How can I do this?

 

[Just Jan]

I think data validation would work
Formula to be used is =COUNTIF(A:A,A1)=1
If number only =AND(COUNTIF(A:A,A1)=1,ISNUMBER(A1))
If text only =AND(COUNTIF(A:A,A1)=1,ISTEXT(A1) )

Apply to column but be aware that data already entered may have to be checked visually or a sort used to bring duplicates together. Any new data will have to satisfy the condition.

 

[doofusboy]

Something like this should work for you:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then
        Range("B" & Target.Row - 1).Copy
        Range("B" & Target.Row).PasteSpecial
    End If
End Sub

 

[goss]

Thanks Doofusboy,

Works great!