+ Reply to Thread
Results 1 to 5 of 5

Lookup data in table and check date and time from 2 different cell references

  1. #1
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    359

    Lookup data in table and check date and time from 2 different cell references

    Good afternoon guys

    I have a sheet that contains a training "Roster". I need to compare a data list to make sure that it matches the data on roster, however they are in 2 very different formats.

    On the attached file, the sheet "Schedule" shows names, times and dates for people rostered on. Each person has a unique staff ID, however each person may appear multiple times throughout the sheet on different days.

    What I am trying to acheive is to look for the staff number on a particular date and time, and if it matches return a "Y" value. However where i am running into problems is each date has 3 seperate lines where the person may appear, as well as a number of different time slots.

    See the attachment on sheet "Schedule"
    The times could start at 7:30, 11:40, 15:50 or 20:00.
    The dates are listed in column A, but as you can see each date applies to 5 rows of data (this sheet is produced by another department and cannot be altered, we just get a read only copy).

    As an example of the outcome :
    Sheet "Names" Row 26-32 has Doug Barnes with employee # 1350.
    In column L I would like to return a "Y" value if the times listed for him match to the "Schedule", so I would look up the date and time, and see if any of the 5 rows have his employee number in them (it will be a part of the name, not the whole cell).

    I have only included a small part of each sheet, however the entry in cell L7 on the Schedule tab should return a "Y" in the yellow highlighted cell row 26 of the names sheet, as it is a 7:30 start on 22/07/2013 for employee number 1350.

    Hope this explains what I am after.

    Darren
    Attached Files Attached Files
    Handy things to keep in mind:

    Click *, if my suggestion has helped you
    If your problem is solved, then please mark the thread as SOLVED

    Sharing is Caring .... spread the knowledge

  2. #2
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    359

    Re: Lookup data in table and check date and time from 2 different cell references

    72 views and no replies ... i think that is my record !!

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,635

    Re: Lookup data in table and check date and time from 2 different cell references

    i didnt quite understand the question and since i've been in training all week it was abit too complex for me to delve into hahaha

    anyways had a better look now
    it may require that building of block array i did for your previous questions by the sounds of it but back to first point...still dont quite understand the question/expected result
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    359

    Re: Lookup data in table and check date and time from 2 different cell references

    Sorry, reasding it i dont think i understand how i asked it either.

    I need to go through the sheet, find the staff number of a person in the list and return the date and time. The problem being the date has 3 lines of data applying to each date, and the times could be any of the start times across the top of the page.

    As it is a cross check, I wanted to make it a simple yes or no type result.

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,635

    Re: Lookup data in table and check date and time from 2 different cell references

    your simple Y/N are never that simple

    i think i under the Y/N bit
    Please Login or Register  to view this content.
    so basically IF Countif>0 then Y otherwise N

    indirect section is build the block array based on the start date (F2) ie Schedule!A58:AE62
    *B2* is wildcard search on staff num
    countif to see if any hits
    Attached Files Attached Files
    Last edited by humdingaling; 07-22-2013 at 03:49 AM.

+ 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] VBA to Check if Cell Formula References Another Sheet
    By learning_vba in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-07-2013, 02:44 PM
  2. Lookup Date in Table & Display Cell Information
    By jayjacko in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-02-2013, 04:54 PM
  3. Check value appears in table (lookup?)
    By dan_manchester in forum Excel General
    Replies: 2
    Last Post: 01-13-2013, 01:10 PM
  4. Replies: 3
    Last Post: 03-25-2010, 10:20 PM
  5. [SOLVED] Lookup in table with two equal references
    By Maxtrix in forum Excel General
    Replies: 4
    Last Post: 12-28-2005, 07:20 AM

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