Lookup date of last score entered.

jpittari

New Member
Joined
Oct 6, 2008
Messages
19
In the example below I have enter in a data array the scores for various playoff games as they happened. The date in column A is the scheduled game date. Some games are rained out, some get scores. In the title of the summary sheet I would like a formula to give me the date of the last game played. In this example that date would be 6/14/2012. This is a dynamic list so every time the data for a completed game (or rain out) is entered then that date should update itself.

Any help would be very much appreciated,

Thanks,
Joe
DATE</SPAN>DAY</SPAN>GAME</SPAN>RO</SPAN>SCORE</SPAN>WIN</SPAN>LOSS</SPAN>TIE</SPAN>
06/06/2012</SPAN>Wednesday</SPAN>Dodgers</SPAN>Rays</SPAN>ü</SPAN>
06/08/2012</SPAN>Friday</SPAN>Phillies</SPAN>Yankees</SPAN> 3</SPAN>7</SPAN>Yankees</SPAN>Phillies</SPAN>
06/09/2012</SPAN>Saturday</SPAN>Dodgers</SPAN>Mets</SPAN> 11</SPAN>12</SPAN>Mets</SPAN>Dodgers</SPAN>
06/09/2012</SPAN>Saturday</SPAN>Rays</SPAN>Cardinals</SPAN>ü</SPAN>
06/09/2012</SPAN>Saturday</SPAN>Red Sox</SPAN>A's</SPAN>ü</SPAN>
06/09/2012</SPAN>Saturday</SPAN>Yankees</SPAN>Giants</SPAN>ü</SPAN>
06/11/2012</SPAN>Monday</SPAN>Mets</SPAN>Phillies</SPAN> 7</SPAN>8</SPAN>Phillies</SPAN>Mets</SPAN>
06/12/2012</SPAN>Tuesday</SPAN>Giants</SPAN>Red Sox</SPAN> 11</SPAN>21</SPAN>Red Sox</SPAN>Giants</SPAN>
06/13/2012</SPAN>Wednesday</SPAN>Rays</SPAN>Yankees</SPAN> 3</SPAN>14</SPAN>Yankees</SPAN>Rays</SPAN>
06/14/2012</SPAN>Thursday</SPAN>Cardinals</SPAN>Mets</SPAN> 15</SPAN>9</SPAN>Cardinals</SPAN>Mets</SPAN>
06/15/2012</SPAN>Friday</SPAN>A's</SPAN>Giants</SPAN>
06/18/2012</SPAN>Monday</SPAN>Rays</SPAN>A's</SPAN>
06/19/2012</SPAN>Tuesday</SPAN>Cardinals</SPAN>Red Sox</SPAN>
06/20/2012</SPAN>Wednesday</SPAN>Dodgers</SPAN>Yankees</SPAN>
06/21/2012</SPAN>Thursday</SPAN>A's</SPAN>Mets</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=2><COL span=2><COL><COL span=2><COL span=2><COL></COLGROUP>
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
If your data are in A1:J16
DATE</SPAN>DAY</SPAN>GAME</SPAN>RO</SPAN>SCORE</SPAN>WIN</SPAN>LOSS</SPAN>TIE</SPAN>
06-06-12</SPAN>Wednesday</SPAN>Dodgers</SPAN>Rays</SPAN>ü</SPAN>
06-08-12</SPAN>Friday</SPAN>Phillies</SPAN>Yankees</SPAN> 3</SPAN>7</SPAN>Yankees</SPAN>Phillies</SPAN>
06-09-12</SPAN>Saturday</SPAN>Dodgers</SPAN>Mets</SPAN> 11</SPAN>12</SPAN>Mets</SPAN>Dodgers</SPAN>
06-09-12</SPAN>Saturday</SPAN>Rays</SPAN>Cardinals</SPAN>ü</SPAN>
06-09-12</SPAN>Saturday</SPAN>Red Sox</SPAN>A's</SPAN>ü</SPAN>
06-09-12</SPAN>Saturday</SPAN>Yankees</SPAN>Giants</SPAN>ü</SPAN>
06-11-12</SPAN>Monday</SPAN>Mets</SPAN>Phillies</SPAN> 7</SPAN>8</SPAN>Phillies</SPAN>Mets</SPAN>
06-12-12</SPAN>Tuesday</SPAN>Giants</SPAN>Red Sox</SPAN> 11</SPAN>21</SPAN>Red Sox</SPAN>Giants</SPAN>
06/13/2012</SPAN>Wednesday</SPAN>Rays</SPAN>Yankees</SPAN> 3</SPAN>14</SPAN>Yankees</SPAN>Rays</SPAN>
06/14/2012</SPAN>Thursday</SPAN>Cardinals</SPAN>Mets</SPAN> 15</SPAN>9</SPAN>Cardinals</SPAN>Mets</SPAN>
06/15/2012</SPAN>Friday</SPAN>A's</SPAN>Giants</SPAN>
06/18/2012</SPAN>Monday</SPAN>Rays</SPAN>A's</SPAN>
06/19/2012</SPAN>Tuesday</SPAN>Cardinals</SPAN>Red Sox</SPAN>
06/20/2012</SPAN>Wednesday</SPAN>Dodgers</SPAN>Yankees</SPAN>
06/21/2012


Then based on a column F score try this formula

=LOOKUP(9.99999999999999E+307,1/($F$2:$F$17),$A$2:$A$17)</SPAN>




<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>
 

jpittari

New Member
Joined
Oct 6, 2008
Messages
19
Thank you, this formula will basically work for me. Ideally I would like to be able to take into account if the last reported date involved a rain out game (RO, column E) if that is easy enough to include.

Thanks again for the quick answer,
Joe
 

jpittari

New Member
Joined
Oct 6, 2008
Messages
19
I would like a forula that would report what the date of the last game information was entered, either a played ame or a rain out. The formula above would report the ddat eof the last played game, but if the last entry was a rained out game then that would not be reported.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,947
Messages
5,621,773
Members
415,856
Latest member
jimb2k

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top