+ Reply to Thread
Results 1 to 16 of 16

LOOKUP Problem

  1. #1
    Registered User
    Join Date
    06-27-2013
    Location
    Darwin, Australia
    MS-Off Ver
    Excel 2011
    Posts
    27

    LOOKUP Problem

    Hi all,

    I'm not sure if a LOOKUP formula is the best way to solve my problem, but I have attached a spreadsheet with my work roster on it.

    The 'Roster' sheet has our monthly shifts. The letters for these shifts can't change (I have been told that LOOKUP doesn't do case sensitive).

    The 'Shift Times' sheet indicates how many hours each of the different shifts go for.

    The 'Fatigue Tracker' outlines how many hours we work each day and then works out how many hours we have worked in the last 7 and 28 days. We have maximums we can work during these periods, so what I am trying to do is predict the number of hours each person will work in the future, so we can identify if someone is going to exceed those maximums well before it happens. This then allows us to roster that person for an extra day off for example.

    For Smith, the hours in B2:AF2 have been entered manually but I would like to be able to have it done automatically based on what shift is in the cell range B3:AF3 in the 'Roster' sheet.

    If this is possible, the other problem I have is that when the person is rostered on an N (night) shift, they actually only work 2.5 hours on the day of the N shift and 5.5 the day after. Therefore, I'd like to be able to return 2.5 in the relevant cell in the 'Fatigue Tracker' sheet, but return 5.5 in the cell next to it as well. If this is impossible, then I'd put up with just having the 8 hours returned in the cell of the N shift.

    Another problem is that the order of names in column A in the 'Fatigue Tracker' sheet won't change, but the order will change in column A of the 'Roster' sheet. I know this potentially makes it harder, but I'm hoping someone out there in Excel land can help me out.

    I hope I have explained the situation clearly enough.

    Cheers!
    Attached Files Attached Files

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: LOOKUP Problem

    Hey!

    Put the formula in cells B2 of Sheet Fatigue Tracker and commit by Pressing Ctrl+Shift+Enter and drag it sideways

    =IFERROR(INDEX('Shift Times'!$B$1:$B$5,MATCH(1,(--EXACT(HLOOKUP(DAY(B$1),Roster!$B$1:$AF$6,MATCH($A2,Roster!$A$3:$A$6,0)+2),'Shift Times'!$A$1:$A$5)),0)),"")

    File attached too.

    Are you looking for something of that sort?

    Cheers!

    Deep
    Attached Files Attached Files
    Last edited by NeedForExcel; 07-30-2013 at 08:42 AM.
    Cheers!
    Deep Dave

  3. #3
    Registered User
    Join Date
    06-27-2013
    Location
    Darwin, Australia
    MS-Off Ver
    Excel 2011
    Posts
    27

    Re: LOOKUP Problem

    Thanks, that looks like what I'm after. Is there a way to have the formula be able to search for the surname in the range A3:A6 in the 'Roster' sheet and return the data in the correct row in the 'Fatigue Tracker' sheet? This is so we can move people's names around in the roster sheet without it affecting the formulas in the fatigue tracker.

    Also, do you know if it's possible to split the hours of the N shift over 2 days? 2.5 hours on the day of the N shift (which is obviously easily done by changing the number in the 'Shift Times' sheet) but also put 5.5 hours the cell next to where the N shift was?

    Cheers,

    Dougie

  4. #4
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: LOOKUP Problem

    Hey!

    Is there a way to have the formula be able to search for the surname in the range A3:A6 in the 'Roster' sheet and return the data in the correct row in the 'Fatigue Tracker' sheet?
    Thats is what the Hlook up is doing. I have attached the file in the above post. Just give it a look.

    In the meantime I am working on the 2nd bit.

    Cheers!

    Deep

  5. #5
    Registered User
    Join Date
    06-27-2013
    Location
    Darwin, Australia
    MS-Off Ver
    Excel 2011
    Posts
    27

    Re: LOOKUP Problem

    Awesome, that looks like it is exactly what I'm after. Thanks very much!

    It would be even more amazing if you are able to figure out the splitting hours problem for the N shift.

    Dougie

  6. #6
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: LOOKUP Problem

    Here!

    Put the formula in cells B2 of Sheet Fatigue Tracker and commit by Pressing Ctrl+Shift+Enter and drag it sideways

    =IF(A2=2.5,6.5,IF(HLOOKUP(DAY(B$1),Roster!$B$1:$AF$6,MATCH($A2,Roster!$A$3:$A$6,0)+2)="N",2.5,IFERROR(INDEX('Shift Times'!$B$1:$B$5,MATCH(1,(--EXACT(HLOOKUP(DAY(B$1),Roster!$B$1:$AF$6,MATCH($A2,Roster!$A$3:$A$6,0)+2),'Shift Times'!$A$1:$A$5)),0)),"")))

    I have also attached the file. I was just wondering, N = 9 as per the sheet attached, then why would you divide it like 2.5 & 5.5 as mentioned in #3?? Shudn't it be 2.5 & 6.5??

    Hope it helps!

    Deep
    Attached Files Attached Files
    Last edited by NeedForExcel; 07-30-2013 at 11:03 AM.

  7. #7
    Registered User
    Join Date
    06-27-2013
    Location
    Darwin, Australia
    MS-Off Ver
    Excel 2011
    Posts
    27

    Re: LOOKUP Problem

    Oops, the N shift should actually be 8 hours, not the 9 that I had in the 'Shift Times' sheet.

    Thank you so much for doing this for me. You are amazing!

    Cheers,

    Dougie

  8. #8
    Registered User
    Join Date
    06-27-2013
    Location
    Darwin, Australia
    MS-Off Ver
    Excel 2011
    Posts
    27

    Re: LOOKUP Problem

    There is also another question I would like to ask you.

    In the 'Fatigue Tracker' sheet, next to 7 days and 28 days for each person, I would like to be able to use a formula for adding the previous 7 days and 28 days hours to a cell. Currently there is a formula in C3 saying =SUM(B2:C2). Then in K2 that formula is now =SUM(E2:K2) because someone has manually counted the correct number of cells back so that K3 includes the previous 7 days worth of hours.

    I was hoping to be able to use a formula to do this automatically so there is no chance for an error. Is this possible?

    Dougie

  9. #9
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: LOOKUP Problem

    I cant think of a Formula to do that with the arrangement you have...

    Are you fine with VBA?

    Deep

  10. #10
    Registered User
    Join Date
    06-27-2013
    Location
    Darwin, Australia
    MS-Off Ver
    Excel 2011
    Posts
    27

    Re: LOOKUP Problem

    I'm not going to worry about it because I forgot that not having the $ in the formula will make it not absolute.

    I have encountered another problem though. I forgot to mention that I only have Excel 2003 at work and when I opened the file you attached in your previous post, I get an #NAME? error in every cell. When I checked the formula, it had changed it slightly to be (in bold):

    =IF(A2=2.5,6.5,IF(HLOOKUP(DAY(B$1),Roster!$B$1:$AF$6,MATCH($A2,Roster!$A$3:$A$6,0)+2)="N",2.5,_xlfn.IFERROR(INDEX('Shift Times'!$B$1:$B$5,MATCH(1,(--EXACT(HLOOKUP(DAY(B$1),Roster!$B$1:$AF$6,MATCH($A2,Roster!$A$3:$A$6,0)+2),'Shift Times'!$A$1:$A$5)),0)),"")))

    Not sure why it put that in there but when I remove it to use your exact formula I still get the #NAME? error.

    Is there something else I'm doing wrong or won't the formula work with Excel 2003?

  11. #11
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: LOOKUP Problem

    That is because, Functions like Iferror do not work with Excel 2003..

    Deep

  12. #12
    Registered User
    Join Date
    06-27-2013
    Location
    Darwin, Australia
    MS-Off Ver
    Excel 2011
    Posts
    27

    Re: LOOKUP Problem

    Damn it, so I'm guessing there is no way to do it in Excel 2003 using other formulas then?

  13. #13
    Registered User
    Join Date
    06-27-2013
    Location
    Darwin, Australia
    MS-Off Ver
    Excel 2011
    Posts
    27

    Re: LOOKUP Problem

    I have attached a solution that someone from work came up with but he has used ASCII codes to convert the shifts to hours. It doesn't work for all of the shifts (there were a few that I forgot about).

    Do you have any idea how to fix this problem?

    I also liked your solution of being able to search for the name in 'Roster' in column A so it doesn't matter what order the names are in. The attached version doesnt have that option.

    Dougie

  14. #14
    Registered User
    Join Date
    06-27-2013
    Location
    Darwin, Australia
    MS-Off Ver
    Excel 2011
    Posts
    27

    Re: LOOKUP Problem

    Oops, attached now.
    Attached Files Attached Files

  15. #15
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: LOOKUP Problem

    Well!

    The solution provided by you friend is very good. Short & Sweet. I never thought we could use the Code function. You can integrate both the solution so that it will be simpler as well as connected to the employee name.

    Deep

  16. #16
    Registered User
    Join Date
    06-27-2013
    Location
    Darwin, Australia
    MS-Off Ver
    Excel 2011
    Posts
    27

    Re: LOOKUP Problem

    I've tried integrated them both but can't figure out how to do it. I don't suppose you'd be able to help me combining the two!

    Dougie

+ 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. lookup problem
    By meteor in forum Excel General
    Replies: 3
    Last Post: 01-30-2007, 09:09 PM
  2. LOOKUP problem!!!
    By Anne Troy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-06-2005, 05:05 PM
  3. [SOLVED] LOOKUP problem!!!
    By G in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 PM
  4. LOOKUP problem!!!
    By G in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  5. Lookup() problem
    By Wayman Bell in forum Excel General
    Replies: 11
    Last Post: 03-03-2005, 12:55 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1