+ Reply to Thread
Results 1 to 2 of 2

Find Last Cell in Matched Column with data gaps and Reference cell in column D

  1. #1
    Forum Contributor
    Join Date
    04-21-2006
    Location
    Australia
    MS-Off Ver
    O365 PC Version 2301
    Posts
    122

    Find Last Cell in Matched Column with data gaps and Reference cell in column D

    I have a sheet 'Futsal Player Stats' showing data for games that players have played including goals etc. What I am trying to do is find the first and last date that the person played. The info is on the 'Futsal Goalscoring' sheet and the date is in column D.
    I'm trying to make a formula do the following;
    • Find the Column that the person is entered in
    • Using two separate formulas, find the first and last cell in that column that is not empty (noting that there are gaps)
    • Reference that cell (row number) and find the date in column D corresponding to it

    The formula I currently have for finding the last entry is as follows;
    Formula 1
    Please Login or Register  to view this content.
    This is giving me a #N/A error.

    I tried using the vlookup to put the column letter into the formula but would prefer a simpler solution. This was an attempt to adjust the formula
    Formula 2
    Please Login or Register  to view this content.
    I may need two formulas to make it work, one to get the number reference of the row and another using that to index it giving the date.

    For finding the first entry I was using;
    Formula 3
    Please Login or Register  to view this content.
    This also needs to have a volatile column instead of $M based on indexing from column D of the current sheet. I think it is not working with columns that have gaps as well.

    From that formula I was using this one to get the date.
    Formula 4
    Please Login or Register  to view this content.
    Formula 4 works when $AJ7 has a value in it.$AJ7 is the value from formula 3.

    Hopefully I've explained this well enough to get the idea. If it can be done with a single formula to get the date great. If not I just need formula to work for first and last row so I can get that to work with formula 4.

    Thanks

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Find Last Cell in Matched Column with data gaps and Reference cell in column D

    Please upload a representative Excel workbook. The attachment icon does not work. The proper instructions are in the gold banner across the top of this page.
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Workbook_Open - pick a value from cell reference & find in a Column and go to that cell
    By excel_googler in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-28-2018, 07:01 PM
  2. Replies: 9
    Last Post: 08-24-2017, 09:16 PM
  3. Replies: 2
    Last Post: 09-21-2013, 07:24 PM
  4. Replies: 12
    Last Post: 12-28-2012, 07:49 PM
  5. Replies: 3
    Last Post: 10-31-2012, 12:05 AM
  6. [SOLVED] Find a Value in Row and Reference a Cell in Same Column as Value
    By TylerJamison in forum Excel General
    Replies: 4
    Last Post: 08-14-2012, 11:44 AM
  7. [SOLVED] Column Reference-how do I find a cell containing a certain value and get?
    By Hugh Murfitt in forum Excel General
    Replies: 0
    Last Post: 02-09-2006, 12:00 PM

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