+ Reply to Thread
Results 1 to 7 of 7

Lookup Formula with 2 criteria to build schedule

  1. #1
    Registered User
    Join Date
    05-06-2014
    Location
    United States
    MS-Off Ver
    Office Professional 2013
    Posts
    3

    Lookup Formula with 2 criteria to build schedule

    Hello,
    I've searched high and low and can not come up with a way to solve this. I have a schedule that covers 3 shifts for the entire month. I have another sheet that is used for crew sign in that I want to auto populate with all the people on that shift for that day.

    I need to be able to use 2 different cells on the sign in sheet for reference for the formula (one for the day, and the other for the shift. ex. Day 3rd, shift D). I'm wanting to use an array formula to accomplish this. All help is appreciated. I've uploaded a stripped schedule as this is for the military and can't have any data on it that pertains to what or who it's for.

    BTW our current method is to due it manually for every shift for every day that wastes about 12-16 man hours every week. I have excel 2010 at work currently. Access is out of the question and Macro's/vba may or may not work as security is always being increased on our systems so either a single or multiple formula is the route I'm looking to do that will work the the next few years I hope. Again thank you in advance for all help given. I seem to learn new functions in Excel on a weekly basis.
    Last edited by Maglin; 05-06-2014 at 07:43 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Lookup Formula with 2 criteria to build schedule

    Your sample is incomplete and difficult to understand.

    1) The names of employees are represented where? A,B,C? Perhaps Name1, Name2,Name3 is more obvious junk names.
    2) Your sign in sheet does not demonstrate the desired result. Mock up a solution manually and completely, make sure it's clear what is supposed to be automated and what is referred to when the data is pulled in.

    You might need to create several examples of the Sign In sheet to fully demonstrate all the hurdles that have to be overcome.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    05-06-2014
    Location
    United States
    MS-Off Ver
    Office Professional 2013
    Posts
    3

    Re: Lookup Formula with 2 criteria to build schedule

    Sorry this is the first time I've ever had to ask for help.

    I took your suggestion and changed the document and added comments. Along the top of the schedule is numbers that correspond to the date which is one field I want to be able to specify. Then I want to search down that date column for the shift (ie. D) and put the people on that shift into the Assigned Crewmember column. Along with that I would also like to get their qualifications from column B of the schedule and put that in the position Qual (column B) on the Crew Sign in sheet. After rereading it I realized how I didn't make sense. I hope I'm a little more clear now.
    Attached Files Attached Files

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Lookup Formula with 2 criteria to build schedule

    Why are names 1-13 included in the result and not others. Exactly why, for each name?

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Lookup Formula with 2 criteria to build schedule

    For instance, using AE1 and AE2 as the search criteria, I get names 1-11 using a standard multiple-row detection array.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-06-2014
    Location
    United States
    MS-Off Ver
    Office Professional 2013
    Posts
    3

    Re: Lookup Formula with 2 criteria to build schedule

    That was me doing what I do at work and manually putting them into the Sign in Sheet as they are day shift just for a different position all together. So you used:
    Please Login or Register  to view this content.
    There is more there than I can figure out how it's working. It works beautifully. The few TAC people can be manually input as they are always just 2-3 per shift. Thank you very much for doing this. If it's not to complex would you mind going over how this works or point me to some tuturials I can read up on how to make formula's like this? Again thank you so very much.

    Edit: NM. Just Googled Excel multiple-row detection array and found a few examples that are well explained. I was searching for the wrong thing.
    Last edited by Maglin; 05-06-2014 at 08:32 PM.

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Lookup Formula with 2 criteria to build schedule

    I've marked this thread as SOLVED for you.

    Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.

+ 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] Struggling to build count/countif/countifs formula with various comparative criteria!
    By dropanddrive03 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-16-2014, 03:04 AM
  2. Replies: 11
    Last Post: 02-07-2013, 02:22 AM
  3. Creating a Build Schedule
    By cabroncito29 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-01-2013, 04:20 PM
  4. Replies: 1
    Last Post: 05-16-2011, 05:00 PM
  5. Help me to build IF function for my production schedule>>>URGENT>>>>
    By sakuramalaya200 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-19-2005, 12:18 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