One way to try ..
(Link to a sample file is provided below)
Assuming the table below is in Sheet1, in A1:F13, where there are 2 blank
rows just below the headers and data starts in row4 down. Assume new rows
as required will always be inserted at row3 and the dates in col A are in
descending order (Latest date on top)
Date 1st 2nd 3rd 4th 5th
(blank row)*
(blank row)
28-May-05 5 7 24 28 39
25-May-05 27 29 31 42 46
21-May-05 2 5 7 10 30
18-May-05 7 13 17 20 35
14-May-05 21 29 38 49 52
11-May-05 5 25 46 48 49
07-May-05 21 23 39 44 47
04-May-05 10 21 27 33 40
30-Apr-05 8 15 17 32 48
27-Apr-05 11 13 18 32 42
*with a label in A2: "(Select row3 below, then click to insert new row)"
With the table below is in Sheet2, where the numbers 1 - 49 are listed down
in A2:A50, col B contains your existing calcs for Times Picked, and col C is
where the last drawn date for the number in col A is desired
Number Times Picked Last Drawn
01 0
02 1
....
48 2
49 2
Put in the formula bar for C2, array-enter with CTRL+SHIFT+ENTER):
=IF(ISNA(MATCH(1,((Sheet1!$B$3:$B$13=A2)+(Sheet1!$ C$3:$C$13=A2)+(Sheet1!$D$3
:$D$13=A2)+(Sheet1!$E$3:$E$13=A2)+(Sheet1!$F$3:$F$ 13=A2)),0)),"",INDEX(Sheet
1!$A$3:$A$13,MATCH(1,((Sheet1!$B$3:$B$13=A2)+(Shee t1!$C$3:$C$13=A2)+(Sheet1!
$D$3:$D$13=A2)+(Sheet1!$E$3:$E$13=A2)+(Sheet1!$F$3 :$F$13=A2)),0)))
Copy C2 down to C50
Col C will return the last drawn dates
The formulas in col C will auto-adjust to cover the extended ranges when you
insert new rows in Sheet1 (with row3 selected) to input on-going results
Here's a sample file with the implemented construct:
http://flypicture.com/p.cfm?id=55451
(Right-click on the link: "Download File"
at the top in the page, just above the ads)
File: gonowhere_misc_1.xls
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"gonowhere" wrote
in message ...
Hi, new to the forum but I've been reading it for a couple of days now.
Looking forward to talking and learning with everyone. I think I am
better than average with Excel but this problem is driving me nuts! I
have a list of lottery numbers listed by date drawn (see below for
example) on one worksheet. On another sheet I have a count of how many
times a number has been drawn. I would like to add a column to show the
last date a number was drawn and have it update automatically when I add
a new drawing. However, I haven't been able to figure out how to get
the date to update automatically.
_Example:_
Date 1st 2nd 3rd 4th 5th
28-May-05 05 07 24 28 39
25-May-05 27 29 31 42 46
21-May-05 02 05 07 10 30
18-May-05 07 13 17 20 35
14-May-05 21 29 38 49 52
11-May-05 05 25 46 48 49
07-May-05 21 23 39 44 47
04-May-05 10 21 27 33 40
30-Apr-05 08 15 17 32 48
27-Apr-05 11 13 18 32 42
Number Times Picked
01 4
02 2
03 2
04 2
05 6
06 4
07 8
08 3
09 8
10 8
The dates and each number appears in their own cells across six columns
(A thru F). I used COUNTIF to get the total number of times picked.
Long story short, I would like to add a third column to show the last
date that a number was picked. So, for example, if the numbers "3, 5,
7, 8 and 10" were drawn on "1 June 05" the "date drawn" column would
show the 1 June date for those 5 numbers. I hope someone can help me.
Btw, I know from reading some other posts that the lottery is not a
favorite subject to discuss with some people but rest assured that I
only do it for fun. I know there is nothing I can do in Excel that
will help me win a random draw lottery.
Thanks in advance for any help,
Mike
--
gonowhere
------------------------------------------------------------------------
gonowhere's Profile:
http://www.excelforum.com/member.php...o&userid=23849
View this thread: http://www.excelforum.com/showthread...hreadid=374986