I defined a name for MyTable and NumberRange and use this to countif and
find last drawing.

Sub Upate()
On Error Resume Next
For Each c In Range("NumberRange")
c.Offset(, 1) = Application.CountIf(Range("MyTable"), c)
c.Offset(, 2) = Range("MyTable").Find(c, after:=Sheets("drawing"). _
Range("b2"), lookat:=xlWhole, searchorder:=xlByRows).Row - 2
Next
End Sub
Sub NewRow()
Sheets("drawing").Rows(3).Insert
End Sub

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
I looked at your workbook. I would
1. sort by last at the top with one intervening row that is hidden,
inserting each time at row 3
2. use a for each macro to find each number from the other page such as
this
example.

Sub findem()
for each c in numberrange
c.offset(,1)=application.CountIf(Range("mt"), c)
c.offset(,2) = Cells.Find(c, after:=Range("a2"), _
lookat:=xlWhole, searchorder:=xlByRows).Row - 2
next
End Sub

--
Don Guillett
SalesAid Software

"knoertje " wrote in message
...
On one sheet I keep track of the lottery numbers every week. On another
sheet I try to keep the statistics of these numbers, like how many
times and how many drawings ago a number ........ The how many times
was no problem, with a COUNTIF....but the how many drawings ago,
doesn't work. I tried an IF (sheet1!A1:A6="number",sheet2!A1+1,
sheet2!=1. But then I get a circular reference statement. How do I
proceed?????


---
Message posted from http://www.ExcelForum.com/