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 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

[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" <gonowhere.1ptwia_1117429501.3006@excelforum-nospam.com> wrote
in message news:gonowhere.1ptwia_1117429501.3006@excelforum-nospam.com...
>
> 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
>

[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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"gonowhere" <gonowhere.1ptwia_1117429501.3006@excelforum-nospam.com> wrote
in message news:gonowhere.1ptwia_1117429501.3006@excelforum-nospam.com...
>
> 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
>

[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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"gonowhere" <gonowhere.1ptwia_1117429501.3006@excelforum-nospam.com> wrote
in message news:gonowhere.1ptwia_1117429501.3006@excelforum-nospam.com...
>
> 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
>

[Max]

In Sheet2, format C2 as "date" before copying down
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[James Malone]

Worked like a charm. Thanks.

"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
news:u6oDsqOZFHA.2128@TK2MSFTNGP15.phx.gbl...
> 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.
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "gonowhere" <gonowhere.1ptwia_1117429501.3006@excelforum-nospam.com> wrote
> in message news:gonowhere.1ptwia_1117429501.3006@excelforum-nospam.com...
>>
>> 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
>>
>

[Ragdyer]

Thanks for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"James Malone" <gonowhere@cox.net> wrote in message
news:5GGme.135$Zt.55@okepread05...
> Worked like a charm. Thanks.
>
> "Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
> news:u6oDsqOZFHA.2128@TK2MSFTNGP15.phx.gbl...
> > 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.
> > --
> > HTH,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> > "gonowhere" <gonowhere.1ptwia_1117429501.3006@excelforum-nospam.com>
wrote
> > in message
news:gonowhere.1ptwia_1117429501.3006@excelforum-nospam.com...
> >>
> >> 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
> >>
> >
>
>