+ Reply to Thread
Results 1 to 8 of 8

Return date of next item due

  1. #1
    Registered User
    Join Date
    08-07-2013
    Location
    Smyrna, GA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Return date of next item due

    I have a 24-tab worksheet. Each tab references information about a deliverable to our customer.

    Example:
    Filename | Due Date | Submission Date
    Name1 | 06SEP13 | -
    Name2 | 07OCT13 | -
    Name3 | 07NOV13 | -

    I'd like to have a cell at the top of each of the tabs to tell me when the next due date is. Here's what I've come up with so far, and I'm absolutely positive I'm over-thinking this, but I've hit a roadblock and need some fresh eyes on it:
    =ADDRESS(MATCH(TODAY(),$B$4:$B$369,-1),2,4)

    The value seems to return the correct cell address, B4. Now, I just need the contents of that address. When I wrap the above in =CELL("contents", <above_formula>), it returns an error.

    What am I missing? Is there a shortcut that would make this simpler?

    The end result will be that the first tab of the worksheet would reference each tab's "next due date" result of the formula above so that I can report status and project my deliverables.

    Thanks in advance from a new user on the forum - hopefully I can pay the help forward.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Return date of next item due

    Hi
    perhaps = INDEX($B$4:$B$369,MATCH(TODAY(),$B$4:$B$369,-1))

  3. #3
    Registered User
    Join Date
    08-07-2013
    Location
    Smyrna, GA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Return date of next item due

    Pepe Le Mokko,
    Thanks - I think that got me a bit closer. Being completely unfamiliar with INDEX, I'm not sure...

    However, the function you gave above returns the last deliverable due date, not the first. I think you're headed in the right direction, though...

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Return date of next item due

    Please post a sample sheet with expected results - Thx

  5. #5
    Registered User
    Join Date
    08-07-2013
    Location
    Smyrna, GA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Return date of next item due

    I am unable to do so quickly. Unfortunately it's for government work and some information cannot be posted.

    I was able to copy/paste a portion out of Excel here, which does not include information that would not be allowed.

    Your formula returns the "06-Dec-13" value at the top right. It's picking the last date of in column B, when I need it to pick the first date in column B, or more accurately, the first date in column B which comes after today's date.

    My formula returns the "B4" value in the top middle. B4 is the cell address of "6-Sep-13" which is the value I'm looking for as the result of the formula.

    Next Scheduled Delivery: B4 06-Dec-13
    ID Due Date
    20_PM_QM_RE_A001_130906 6-Sep-13
    20_PM_QM_RE_A001_131007 7-Oct-13
    20_PM_QM_RE_A001_131107 7-Nov-13
    20_PM_QM_RE_A001_131206 6-Dec-13

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Return date of next item due

    Try =INDEX(A2:A15,MATCH(MIN(ABS(A2:A15-TODAY())),ABS(A2:A15-TODAY()),0)) entered with Ctrl+Shift+Enter and adapt ranges to your needs

  7. #7
    Registered User
    Join Date
    08-07-2013
    Location
    Smyrna, GA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Return date of next item due

    Thank you so much! It works!
    What does the CTRL-SHIFT-ENTER do?

  8. #8
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Return date of next item due

    It makes the formula an array formula. Read more about it at http://www.xtremevbtalk.com/showthread.php?t=296012 ( and other places)

+ 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. Formula to return last date an item ordered
    By ExcelJunior in forum Excel General
    Replies: 6
    Last Post: 05-17-2011, 12:37 AM
  2. Replies: 4
    Last Post: 03-18-2011, 09:13 AM
  3. Excel 2007 : Return an item from an invoice
    By Bobbyraw in forum Excel General
    Replies: 2
    Last Post: 04-22-2010, 03:52 PM
  4. If column A is a June date, return the dollar value for that line item.
    By dearickmilton in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-09-2009, 08:39 AM
  5. [SOLVED] Match return #NA ...though item exists!
    By Sige in forum Excel General
    Replies: 2
    Last Post: 01-12-2006, 10:50 AM

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