Update Cell Value Based on different Cell

[gonowhere]

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 a
better than average with Excel but this problem is driving me nuts!
have a list of lottery numbers listed by date drawn (see below fo
example) on one worksheet. On another sheet I have a count of how man
times a number has been drawn. I would like to add a column to show th
last date a number was drawn and have it update automatically when I ad
a new drawing. However, I haven't been able to figure out how to ge
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 column
(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 las
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 woul
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
favorite subject to discuss with some people but rest assured that
only do it for fun. I know there is nothing I can do in Excel tha
will help me win a random draw lottery.

Thanks in advance for any help,
Mik

 

[Biff]

Hi!

Let's see if we understand you....

On sheet2 you enter 5 numbers and you want to know the last time that
combination of 5 numbers hit?

Also, I see that your table is in descending order by date. It looks like
you're inserting a new row at the top of your list each time you update it.
Is that true?

Biff

 

[gonowhere]

Hey, thanks for the reply. What I posted was just a sample of the two
sheets. The first sheet has all of the drawings for the last 6 months.
I enter the new drawing at the bottom then sort it by date descending
so the most current is at the top. The second sheet has all of the
numbers in this lottery, which is 1 thru 53, in the first column and
the number of times each number has been picked during the 6 month
period in the second column. I hope this helps clarify things a bit.

 

[Ragdyer]

With your example starting in A1, with your title row,
And your data extending from A2 to F11,
And your *oldest* date at the bottom:

And your number list starting in H2,
And your Countif formula starting in I2,
Enter this *array* formula in J2:

=INDEX($A$1:$A$11,MIN(IF($A$2:$F$11=H2,ROW($A$2:$A$11))))

and double click the fill handle to copy this down Column J as far as there
is data in Column I.
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

If a number in Column H is *not* present in your data list, the formula will
return A1 (DATE).

Also, make sure that Column J is formatted as a date.

 

[Max]

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)+(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)))

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

 

[Max]

In Sheet2, format C2 as "date" before copying down

 

[Max]

Correction wrt insertion point in Sheet1, apologies:
In Sheet1, select row4 before you insert new row (instead of row3)
This ensures the formulas in Sheet2 will auto-cover the extended range

 

[James Malone]

Worked like a charm. Thanks.

With your example starting in A1, with your title row,
And your data extending from A2 to F11,
And your *oldest* date at the bottom:

And your number list starting in H2,
And your Countif formula starting in I2,
Enter this *array* formula in J2:

=INDEX($A$1:$A$11,MIN(IF($A$2:$F$11=H2,ROW($A$2:$A$11))))

and double click the fill handle to copy this down Column J as far as
there
is data in Column I.
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead
of
the regular <Enter>, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.

If a number in Column H is *not* present in your data list, the formula
will
return A1 (DATE).

Also, make sure that Column J is formatted as a date.

 

[Ragdyer]

Thanks for the feed-back.