+ Reply to Thread
Results 1 to 10 of 10

Return the value of a cell based on 4 criteria

  1. 04-24-2015, 11:03 AM #1
    rossi_69
    rossi_69 is offline
    Registered User
    Join Date
    12-16-2008
    Location
    Blackpool, UK
    MS-Off Ver
    2003, 2007, 2010
    Posts
    54

    Return the value of a cell based on 4 criteria

    Hi Everyone,

    I am a relative novice at formulas so any help would be greatly appreciated. I need to construct a formula that returns a value from a cell based on 4 criteria from an exported data sheet.
    I have tried the SUMPRODUCT route but found this to be less than Ideal as major changes to the formulas would be needed when drivers were added and subtracted from the pool.
    Day 1.jpg
    Live.jpg

    I need to return the hours in column H to day1 tab column H based on columns A,B,C,D
    which are Shift number, Driver Number, Day Number, Run Number. The drivers scheduled hours are always applied to the first run of the day.

    Thanks for any help.
    Reply With QuoteRegister To Reply
  2. 04-24-2015, 11:16 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Return the value of a cell based on 4 criteria

    You have attached an image. That's not very easy to work with. We have to re-type your information before we can begin to address your problem. That will put a lot of people off completely.

    Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 04-24-2015, 11:28 AM #3
    rossi_69
    rossi_69 is offline
    Registered User
    Join Date
    12-16-2008
    Location
    Blackpool, UK
    MS-Off Ver
    2003, 2007, 2010
    Posts
    54

    Re: Return the value of a cell based on 4 criteria

    Sorry Here is my file
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 04-24-2015, 11:47 AM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Return the value of a cell based on 4 criteria

    There's something here that doesn't make sense to me.

    In "Live", which columns correspond to Shift number, Driver Number, Day Number and Run Number. Some of them I think I know but others...
    Reply With QuoteRegister To Reply
  5. 04-24-2015, 11:52 AM #5
    rossi_69
    rossi_69 is offline
    Registered User
    Join Date
    12-16-2008
    Location
    Blackpool, UK
    MS-Off Ver
    2003, 2007, 2010
    Posts
    54

    Re: Return the value of a cell based on 4 criteria

    A= Shift Number B= Driver number C= Day Number D= Run Number
    as you can see the green highlighted cell is the hours I want to return in the day 1 tab.
    I need the formula to be dynamic as if we add drivers to different shifts or swap any around we still get the correct information where we need it without having to alter the formulas.

    Thanks for your help Glenn.
    Reply With QuoteRegister To Reply
  6. 04-24-2015, 12:06 PM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Return the value of a cell based on 4 criteria

    SORRY. I asked the wrong Q. Where on "Day 1" are Shift number, Driver Number, Day Number and Run Number. I'm only confident that I've found Day (L2)
    Reply With QuoteRegister To Reply
  7. 04-24-2015, 12:52 PM #7
    rossi_69
    rossi_69 is offline
    Registered User
    Join Date
    12-16-2008
    Location
    Blackpool, UK
    MS-Off Ver
    2003, 2007, 2010
    Posts
    54

    Re: Return the value of a cell based on 4 criteria

    Ok on Day 1 A and B equate to Shift number and Driver Number and run number is not listed as we do not need to display it in columns but you are right L2 is the day number.

    And I got the column to display the result wrong as I had added a column so it is F for the result replacing the SUMPRODUCT FORMULA.
    Reply With QuoteRegister To Reply
  8. 04-24-2015, 01:55 PM #8
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Return the value of a cell based on 4 criteria

    OK. Here you are. I still included the run No in the formula, as (by definition) it's the first one of the day. These are Array Formulas. They're a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 04-24-2015, 02:44 PM #9
    rossi_69
    rossi_69 is offline
    Registered User
    Join Date
    12-16-2008
    Location
    Blackpool, UK
    MS-Off Ver
    2003, 2007, 2010
    Posts
    54

    Thumbs up Re: Return the value of a cell based on 4 criteria

    Thank you so much Glenn

    I must now study this and learn where I was going wrong for the best part of the day.

    Reply With QuoteRegister To Reply
  10. 04-24-2015, 03:03 PM #10
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053
    Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. return a cell value from a table based on multiple criteria
    By mehmetics in forum Excel General
    Replies: 29
    Last Post: 12-08-2014, 11:18 AM
  2. How to return a blank cell based on if then criteria
    By azucar360 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-01-2013, 04:05 PM
  3. Return Cell Value based on Multiple Criteria
    By StuartO in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-13-2013, 06:56 AM
  4. [SOLVED] Return cell value based on criteria of 2 columns
    By Fatnslow in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-03-2012, 04:52 AM
  5. return cell references based on criteria
    By blakeyoung in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-22-2008, 09:10 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1