+ Reply to Thread
Results 1 to 5 of 5

Index question

  1. #1
    Registered User
    Join Date
    07-11-2007
    Location
    Orlando, Florida
    Posts
    48

    Smile Index question

    Hi all!
    I am having a problem with using the index function. I am working (still) on a schedule in excel 2003. I am trying to find a way to take a name off the schedule and put it into a cell on worksheet 2 depending on the time they are scheduled. Here is the formula I am working with, but for some reason it's not reading the time, and sticking a name in that's not even associated with the time.
    Please Login or Register  to view this content.
    Thank you for your time and help!!

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,833
    1. What is the format of the cells D6:D60, time or text?
    2. What is the significance of "-C" in "9:30-C"? You put it in quotes, so it's a text string not a time value.

    An experiment with cell "Dx" formatted as time (hh:mm) and using the formula below returned the correct value from "Bx":
    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    07-11-2007
    Location
    Orlando, Florida
    Posts
    48
    Thank you for the reply. The cells d6-d60 are formatted as text and not as a date/time format. I don't have a choice in this. I have included a sample of what I am doing. On Worksheet 2 I am going to make a sheet that will take each day, Mon - Sun, and list the people working that day for what we use as a "labor chart". What I am trying to do is make the sheets search the schedule, and put the appropriate people in place for the time they are in. The schedule sheet is made, I can make the "Daily sheet", I am having a hard time getting the function to work. Thank you for your time.
    Last edited by desk.doc; 11-25-2007 at 09:39 PM.

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    MATCH, in this case, needs a third argument of zero, i.e.

    =INDEX('FOH Schedule'!B6:B60,MATCH("9:30-C",'FOH Schedule'!D6:D60,0))

    You would only omit the zero (or use 1 or TRUE) if you were looking for a "closest match" in a sorted range. See Excel help on MATCH for more.

  5. #5
    Registered User
    Join Date
    07-11-2007
    Location
    Orlando, Florida
    Posts
    48
    Thank you once again Daddylonglegs. Your help in this sheet has been invaluable. The function you supplied works. I am running into a problem with a second person being scheduled at the same time. If I drag down on the formula, it lists the same person twice instead of moving down to the next person scheduled at this time. Any direction you can point me in on this issue? Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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