+ Reply to Thread
Results 1 to 6 of 6

Lookup Row for the first non-Blank and the last non-blank cell to return column value

  1. #1
    Registered User
    Join Date
    06-22-2020
    Location
    Wantagh, NY
    MS-Off Ver
    365
    Posts
    3

    Lookup Row for the first non-Blank and the last non-blank cell to return column value

    I created a schedule builder (attached) and I want to be able to summarize showing the start time and end time of each employee's shift. I want to refer to the "X"'s on the Schedule tab and show the start time and end time on the Summary tab. I've filled in the Summary tab manually to show what I am looking for.
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Lookup Row for the first non-Blank and the last non-blank cell to return column value

    Employee 1, his shift span over midnight, so each day, he has 2 start and 2 end?
    For instant, Monday: 12:00 AM vs 6:00 AM for 1st shift. How about 2nd shift, which start 8:00PM and end next 6:00 AM of Tuesday?
    Could you give more manual input for Tuesday in Summary sheet?
    Quang PT

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Lookup Row for the first non-Blank and the last non-blank cell to return column value

    As noted above, your desired layout may prove difficult to accommodate unless you choose to record two sets of shifts per day ?

    One alternative, attached, would be to transpose the start/end points into a table - inclusive of day etc.
    this approach will isolate start & end shift times using co-ordinates (of sorts)

    If you had the date value (for the week) then you could use that too, rather than using the literal day of the week string etc.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-22-2020
    Location
    Wantagh, NY
    MS-Off Ver
    365
    Posts
    3
    Ideally the overnight shift would appear on the start day only. So in this case it would have been SAT 8p-6a & Sunday 8p-6a. I can always fill in the overnights manually though if we can figure out the other shifts.


    Quote Originally Posted by bebo021999 View Post
    Employee 1, his shift span over midnight, so each day, he has 2 start and 2 end?
    For instant, Monday: 12:00 AM vs 6:00 AM for 1st shift. How about 2nd shift, which start 8:00PM and end next 6:00 AM of Tuesday?
    Could you give more manual input for Tuesday in Summary sheet?

  5. #5
    Registered User
    Join Date
    06-22-2020
    Location
    Wantagh, NY
    MS-Off Ver
    365
    Posts
    3
    What if we ignore the overnight people and only do the orhers? I can always manually change the overnights if needed.

    Quote Originally Posted by XLent View Post
    As noted above, your desired layout may prove difficult to accommodate unless you choose to record two sets of shifts per day ?

    One alternative, attached, would be to transpose the start/end points into a table - inclusive of day etc.
    this approach will isolate start & end shift times using co-ordinates (of sorts)

    If you had the date value (for the week) then you could use that too, rather than using the literal day of the week string etc.

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Lookup Row for the first non-Blank and the last non-blank cell to return column value

    would "v2" in the attached work for you? this would return start/end times per shift day, where shift day is determined by the the start time

    note: this set-up assumes no individual would start two shifts on the same day {e.g. 0200-1000 and 2000-0400}

    similar setup to before insofar as to keep things as simple as possible, the column # of the various start/end points are identified separately
    {column co-ordinates are stored adjacent to result matrix - with latter just using these # within INDEX calls to return respective time values from schedule}
    Attached Files Attached Files

+ 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. [SOLVED] Lookup a value and return column that has non-blank value
    By shazrn in forum Excel General
    Replies: 8
    Last Post: 05-19-2020, 10:35 AM
  2. [SOLVED] Help getting a V Lookup to return a blank based on cell value
    By m1cks in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-18-2020, 03:22 PM
  3. [SOLVED] V lookup - return value in another column if cell within v-lookup column is blank
    By Jacks18928 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-06-2019, 06:46 AM
  4. Return first non blank cell (cells have formulas that return blank)
    By BG1983 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-05-2016, 04:06 PM
  5. Lookup a blank cell in a Lookup table and return a figure.
    By jonnops in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-20-2014, 07:33 AM
  6. Replies: 0
    Last Post: 01-21-2013, 10:31 AM
  7. [SOLVED] Return of blank cell if lookup fails
    By TimM in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-23-2005, 12:40 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