+ Reply to Thread
Results 1 to 5 of 5

Reference last data entered with date

  1. #1
    Registered User
    Join Date
    12-31-2009
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    3

    Reference last data entered with date

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

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Referencing last data entered with date

    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

  3. #3
    Registered User
    Join Date
    12-31-2009
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Reference last data entered with date

    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?

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Reference last data entered with date

    In N2:

    =IF(COUNTA(B2:K2)=0,"",INDEX($B$1:$M$1,MATCH(99^99,B2:K2,1)))

    copied down.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Registered User
    Join Date
    12-31-2009
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    3

    Talking Re: Reference last data entered with date

    Great! Worked like a charm. Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1