+ Reply to Thread
Results 1 to 2 of 2

Find non-blank cell to the left

  1. #1
    Registered User
    Join Date
    02-10-2015
    Location
    Ohio
    MS-Off Ver
    2007
    Posts
    1

    Find non-blank cell to the left

    I need help building a formula in a RefCell column to find the cell under a column where TODAY() <= the date in column heading.

    My worksheet looks like this:


    Please Login or Register  to view this content.


    EXAMPLES: If TODAY() is in the range of:
    2/9/2015 - 2/11/2015, then RefCell should be F2 and F3, representing Daisy and Curly.
    2/12/2015 - 2/15/2015, then RefCell should be F2 and G3, representing Daisy and Moe.

    The date headers represent the date an on-call person changes. Typically, it's a full week, but if someone takes over mid-week, we will insert a new column with the date it occurs (2/12/2015) and only put the name of the exception.

    I want a formula that will use the current date to return the cell reference that shows who should be on-call. If the cell is blank, then the formula should look to the left to find the first non-blank cell. I want the reference and not the cell value so I can use the reference in other formulas to return other information about the cell.

    Person column will use the RefCell in =INDIRECT(F2) to get the name of the person on-call.
    Phone column, and others, will use VLOOKUP to return information from another tab.

    This is what I have now (Note the -1 on the second line to go back one column).
    It works if the date is 2/12, but not if it's from 2/13-2/15.

    =IF(HLOOKUP(LOOKUP(IF(C$2="",TODAY(),C$2),E$1:H$1),E:H,ROW(),FALSE)="",
    HLOOKUP(LOOKUP(IF(C$2="",TODAY(),C$2)-1,E$1:H$1),E:H,ROW(),FALSE),
    HLOOKUP(LOOKUP(IF(C$2="",TODAY(),C$2),E$1:H$1),E:H,ROW(),FALSE)
    )

    I assume I might need an array formula, but don't know how to construct one to solve my problem.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    Win10/MSO2016
    Posts
    12,967

    Re: Find non-blank cell to the left

    B1
    Please Login or Register  to view this content.
    c1
    Please Login or Register  to view this content.
    Ben Van Johnson

+ 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. Replies: 2
    Last Post: 11-22-2013, 01:07 PM
  2. [SOLVED] Find first non-blank cell after a group of blanks copy offset cells, find next blank, loop
    By gwsampso in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-21-2012, 06:11 AM
  3. macro to find first blank cell in a column cut the value from left adjacent column
    By willykin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-01-2012, 09:23 AM
  4. Making sure a cell is not left blank
    By davecheng in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-23-2009, 09:40 AM
  5. Cell Shouldn't Be Left Blank
    By ravi_m5_2000 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-24-2009, 01:52 PM

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