Reference last data entered with date

**musmin2415** · 12-31-2009, 12:38 AM

Alright, so I'm completely new to this forum, so please be patient.

I'll try to be as clear as possible, but I'm a musician and I keep track of my repertoire (along with how often I perform each song) through a spreadsheet I've generated. Down the first column I've listed the songs, and across the top row I've listed the dates. So, each time I perform a song, I insert a symbol into that cell that will corresponds to the date and song. At the end of each year, I use the 'COUNTIF' function to total up the # of times I've performed the song [example: =COUNTIF(B6:AX6, "●")]

Here's my issue: Right next to my 'Totals' column, I would like to be able to include a column that automatically generates the last date that each song was performed. Is this possible??

**teylyn** · 12-31-2009, 03:17 AM

Hi musmin, fellow musician.

Welcome to the forum,

see attached spreadsheet

I've changed the markers for the songs to a 1 instead of an ●, but I've formatted them to display as a ●.
The totals column uses the formula

=COUNTIF(B2:K2,">0")

so, not much different to what you already had in place.

Next to it, the "Last played" column has the formula

=INDEX($B$1:$L$1,MATCH(99^99,B2:K2,1))

and is formatted as a date.

You need the markers to be numbers in order for the MATCH part to work. If you enter ●, the formula won't work, but if you enter a 1 and format it to look like a ●, then I think you won't miss much.

To insert new dates, highlight the gray column and insert a new column. The formulas should then update automatically.

hth

**musmin2415** · 12-31-2009, 01:43 PM

Incredible....Thank you!!

Now, last question (hopefully --- I can't promise anything). For those songs that I have not played, but have been listed, how do I keep from getting an '#N/A' in the 'Last Played' column?