Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Hello,
Have a bit of an odd but challenging request for the group here! The objective for this project is to take a details work schedule which shows tasks by half hour increment and automatically calculate a summary schedule for the entire week showing the employee's start and end time. I have a simple template for one day of the week which I've uploaded for you to understand the results I'm looking for: https://www.dropbox.com/s/gsfp5tt3uw...late.xlsx?dl=0 In rows 1 - 3, this is the detailed schedule that would be input by the user. They simply enter letters based on the tasks required at the point during their shift. Row 1 shows the time in half hour increments so when Employee 1 has an X in column W, their shift will start at 10:00am. For the same employee, the last X they have for that day is in column AN which means that their shift will end at 19:00 (or 7pm). For Employee 2, the same rules apply but this employee's tasks start in column T which would signify 8:30am and their last X comes in column AK which ends in 17:30. So this top section is fine and somewhat simple. The part I need help with is the bottom section which is the summary, rows 6-10. I want to automate this section based on the detailed schedule being entered in the section above. Ideally, via formulas this summary section would automatically update based on the schedule being entered. The end result would be summary in one cell for the day that shows their start and end time. I've entered examples of this in the spreadsheet and highlighted this in orange. I hope that all makes sense. I should also mention that I cannot use macros here. I've tried to do some sort of lookup using HLOOKUP and INDEX but unable to figure out how to automate the process. Thanks in advance for your help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
please look he
http://www.cjoint.com/c/EJCruf1wCXa isabelle Le 2015-10-28 13:12, isabelle a écrit : hi, it is an array formula to insert with CTRL+Shift+Enter =INDEX($A$1:$AX$1,IFERROR(SMALL(IF($C2:$AX2="x",CO LUMN($C2:$AX2)),1),""))&":"&IF(ISEVEN(IFERROR(SMAL L(IF($C2:$AX2="x",COLUMN($C2:$AX2)),1),"")),"30"," 00")&" - "&INDEX($A$1:$AX$1,IFERROR(LARGE(IF($C2:$AX2="x",C OLUMN($C2:$AX2)),1)+1,""))&":"&IF(ISEVEN(IFERROR(L ARGE(IF($C2:$AX2="x",COLUMN($C2:$AX2)),1),"")),"00 ","30") isabelle Le 2015-10-28 10:41, a écrit : Hello, Have a bit of an odd but challenging request for the group here! The objective for this project is to take a details work schedule which shows tasks by half hour increment and automatically calculate a summary schedule for the entire week showing the employee's start and end time. I have a simple template for one day of the week which I've uploaded for you to understand the results I'm looking for: https://www.dropbox.com/s/gsfp5tt3uw...late.xlsx?dl=0 In rows 1 - 3, this is the detailed schedule that would be input by the user. They simply enter letters based on the tasks required at the point during their shift. Row 1 shows the time in half hour increments so when Employee 1 has an X in column W, their shift will start at 10:00am. For the same employee, the last X they have for that day is in column AN which means that their shift will end at 19:00 (or 7pm). For Employee 2, the same rules apply but this employee's tasks start in column T which would signify 8:30am and their last X comes in column AK which ends in 17:30. So this top section is fine and somewhat simple. The part I need help with is the bottom section which is the summary, rows 6-10. I want to automate this section based on the detailed schedule being entered in the section above. Ideally, via formulas this summary section would automatically update based on the schedule being entered. The end result would be summary in one cell for the day that shows their start and end time. I've entered examples of this in the spreadsheet and highlighted this in orange. I hope that all makes sense. I should also mention that I cannot use macros here. I've tried to do some sort of lookup using HLOOKUP and INDEX but unable to figure out how to automate the process. Thanks in advance for your help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Wow!
Thank you so much, this is perfect. Mercí! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Hi,
Am Wed, 28 Oct 2015 07:41:44 -0700 (PDT) schrieb : The part I need help with is the bottom section which is the summary, rows 6-10. a little bit shorter and without array: =TEXT((VERGLEICH("x";2:2;0)-3)/2/24;"hh:mm")&" - "&TEXT((VERGLEICH("";A2:AX2;-1)-2)/2/24;"hh:mm") Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
MATCH("",A2:AX2,-1)
it's brilliant chapeau! isabelle Le 2015-10-28 13:46, Claus Busch a écrit : Hi, Am Wed, 28 Oct 2015 07:41:44 -0700 (PDT) schrieb : The part I need help with is the bottom section which is the summary, rows 6-10. a little bit shorter and without array: =TEXT((VERGLEICH("x";2:2;0)-3)/2/24;"hh:mm")&" - "&TEXT((VERGLEICH("";A2:AX2;-1)-2)/2/24;"hh:mm") Regards Claus B. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
it's brilliant
chapeau! That's why Claus IS the *formula wizard*!<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Hi again,
Am Wed, 28 Oct 2015 18:46:19 +0100 schrieb Claus Busch: a little bit shorter and without array: =TEXT((VERGLEICH("x";2:2;0)-3)/2/24;"hh:mm")&" - "&TEXT((VERGLEICH("";A2:AX2;-1)-2)/2/24;"hh:mm") sorry, I forgot to translate the formula: =TEXT((MATCH("x",2:2,0)-3)/2/24,"hh:mm")&" - "&TEXT((MATCH("",A2:AX2,-1)-2)/2/24,"hh:mm") Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Hi Isabelle, hi Garry,
Am Wed, 28 Oct 2015 14:39:38 -0400 schrieb GS: it's brilliant chapeau! That's why Claus IS the *formula wizard*!<g thank you for the compliments but I am only a crazy user. First I read the question. Then I look into the table to find structures or sequences I could use to keep the formula short and simple. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Hi Isabelle, hi Garry,
Am Wed, 28 Oct 2015 14:39:38 -0400 schrieb GS: it's brilliant chapeau! That's why Claus IS the *formula wizard*!<g thank you for the compliments but I am only a crazy user. First I read the question. Then I look into the table to find structures or sequences I could use to keep the formula short and simple. Regards Claus B. Well.., that's what makes you the formula wizard! I like short formulas because they're like the one-liner code snippets I like to use... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Thanks so much everyone!
All of these formulas work perfectly for what I need. You are all, indeed formulas wizards! Cheers |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
On Wednesday, 28 October 2015 19:51:33 UTC, Claus Busch wrote:
Hi again, Am Wed, 28 Oct 2015 18:46:19 +0100 schrieb Claus Busch: a little bit shorter and without array: =TEXT((VERGLEICH("x";2:2;0)-3)/2/24;"hh:mm")&" - "&TEXT((VERGLEICH("";A2:AX2;-1)-2)/2/24;"hh:mm") sorry, I forgot to translate the formula: =TEXT((MATCH("x",2:2,0)-3)/2/24,"hh:mm")&" - "&TEXT((MATCH("",A2:AX2,-1)-2)/2/24,"hh:mm") Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Hello, Unfortunately I left out a key part of the spreadsheet so I'm stumped again. I forgot to mention that the detailed schedule will have different tasks than just "X". Here is the list of options that they can enter into their schedule: Task types X S R H I G C A M T O B I've tried to change the match formula to allow for these different options but I can't seem to get it right. Doesn't look like the MATCH formula likes AND or OR to be included. I've updated the example on the dropsite with all of the types of task codes that would be used: https://www.dropbox.com/s/gsfp5tt3uw...late.xlsx?dl=0 Thank you in advance! Daniel |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Hi Daniel,
Am Thu, 29 Oct 2015 11:19:20 -0700 (PDT) schrieb : I forgot to mention that the detailed schedule will have different tasks than just "X". Here is the list of options that they can enter into their schedule: please download you workbook ("schedule template") from: https://onedrive.live.com/redir?resi...=folder%2cxlsm Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Thank you Klaus!
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
"Claus Busch" wrote:
part I need help with is the bottom section which is the summary, rows 6-10. a little bit shorter and without array: =TEXT((VERGLEICH("x";2:2;0)-3)/2/24;"hh:mm")&" - "&TEXT((VERGLEICH("";A2:AX2;-1)-2)/2/24;"hh:mm") Hi Claus. I'm just curious, what the heck is that VERGLEICH function? My version of Excel does not seem to have that function. Thanks. |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Hi Robert,
Am Thu, 12 Nov 2015 00:14:35 -0700 schrieb Robert Crandal: Hi Claus. I'm just curious, what the heck is that VERGLEICH function? My version of Excel does not seem to have that function. I am working with a german language version and forgot to translate the formula. VERGLEICH = MATCH Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Hello Everyone,
I'm back with a refinement on my original request. The function has been working great for me but know I want to limit which hour types will be picked up into this calculation. Here is Klaus' formula that works fine (file called schedule template): https://www.dropbox.com/s/bofbx5ph55...mula.xlsx?dl=0 The formula in question is: =TEXT((MATCH("*",C2:AZ2,0)-1)/48,"hh:mm")&" - "&TEXT((MATCH("",A2:AY2,-1)-2)/48,"hh:mm") The key bit that I'd like to change is the MATCH section of the formula which essentially says when you find any character in this array begin the start of the shift. This is done via "*" in MATCH. What I'd like to do is have MATCH not use "*" but only look for the following characters X, S, R, H, G, C, A, M, T, B. I've tried to insert AND and OR but to no success. Can anyone help? |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Hi David,
Am Tue, 15 Mar 2016 10:08:03 -0700 (PDT) schrieb : The key bit that I'd like to change is the MATCH section of the formula which essentially says when you find any character in this array begin the start of the shift. This is done via "*" in MATCH. What I'd like to do is have MATCH not use "*" but only look for the following characters X, S, R, H, G, C, A, M, T, B. try it with an UDF. Please look he https://onedrive.live.com/redir?resi...=folder%2cxlsm for "schedule template" and download the file because macros are disabled in OneDrive. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Thank you Claus.
Is there any way to take the last part of the formula and have it use the same logic defined in the UDF? The formula you've given me is: =stime(C64:AX64)&" - "&TEXT((MATCH("",A64:AY64,-1)-2)/48,"hh:mm") The current formula works perfect for the "start time" of the schedule [stime(C64:AX64)]but the last part of the formula [TEXT((MATCH("",A64:AY64,-1)-2)/48,"hh:mm")] accounts for all hour types [""]. I'd like to make it such that the "end time" or last part of this formula Here is an example of a shift which has hour types which would be include (like X) and then excluded (like I): https://www.dropbox.com/s/ui4dtkwprv...1616.xlsm?dl=0 I've tried to set up another UDF (eTime) but I obviously don't know what I'm doing as I'm getting error messages! Thanks again for your help. |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Hi Daniel,
Am Wed, 16 Mar 2016 04:44:59 -0700 (PDT) schrieb Daniel Petta: Is there any way to take the last part of the formula and have it use the same logic defined in the UDF? I put sTime and eTime in one UDF. Now you have to call the function in the sheet with: =myTime(C2:AX2) Have a look: https://onedrive.live.com/redir?resi...=folder%2cxlsm for "PLANNER 031616" and download the file. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Wow.
Thanks Claus, I really appreciate it. I would have never been able to figure that out. I really like this UDF thing! Thank you again. |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Hi Daniel,
Am Wed, 16 Mar 2016 06:15:19 -0700 (PDT) schrieb Daniel Petta: Thanks Claus, I really appreciate it. I would have never been able to figure that out. I really like this UDF thing! you are welcome. Always glad to help. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Hello Claus,
I'm having some very odd calculations on the file now that I'm hoping you can help me with. Using the "myTime" formula, I have used this to create a summary of the detailed schedule the week. Each week has it's own tab and the summary can be found at the bottom of each tab where it essentially pulls the detailed schedule from above. The odd situation I'm finding is that weeks in the future, say week #8 is showing summary data where the "myTime" formula is being used even though there is no detailed data to support this. Here is an example of what I'm talking about: https://www.dropbox.com/s/a35u89yaop...file.xlsb?dl=0 It's truly odd! It would appear that each subsequent week is somehow using the previous week's information to fill in the summary schedule. As you can see in the example, the detailed schedule for week #1 is being shown on weeks #2-22 where those weeks have no detailed schedules yet. I've looked at weeks 2-22 and there doesn't appear to be any data in them however, if I select the area where the detailed schedules are entered, clear the contents and the detailed schedule goes away. I've also noticed that if I were to delete one of the tabs then all of sudden these summary schedules go back to correctly not showing anything almost giving me the idea that the formulas were not automatically calculating but this isn't the case. I'm a bit worried as to how it got there in the first place and even if I were go to into the file and clear the contents for future weeks detailed schedules that this won't happen again. Should note that this file is shared on a network and I have unprotected the sheets to allow you to see the formulas although under normal circumstances I would protect each sheet to ensure the store can't edit it. Again, any help you have would be greatly appreciated. I really can't figure out why these summary schedules are showing up when there is no data to support it. |
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Hi Daniel,
Am Thu, 31 Mar 2016 09:56:54 -0700 (PDT) schrieb Daniel Petta: Using the "myTime" formula, I have used this to create a summary of the detailed schedule the week. Each week has it's own tab and the summary can be found at the bottom of each tab where it essentially pulls the detailed schedule from above. The odd situation I'm finding is that weeks in the future, say week #8 is showing summary data where the "myTime" formula is being used even though there is no detailed data to support this. Here is an example of what I'm talking about: https://www.dropbox.com/s/a35u89yaop...file.xlsb?dl=0 please look he https://onedrive.live.com/redir?resi...=folder%2cxlsm for "16000 - FY2017 Store Planning File_V1.2" You have to download the file because macros are disabled in OneDrive. Select week 8 and to the new formula. I improved the function and changed the formula there. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Hi Daniel,
Am Thu, 31 Mar 2016 19:45:39 +0200 schrieb Claus Busch: please look he https://onedrive.live.com/redir?resi...=folder%2cxlsm for "16000 - FY2017 Store Planning File_V1.2" please look again in OneDrive for "16000 - FY2017 Store Planning File_V1.3" I improved again the function that you don't need the IF statement. Now you can use myTime(...) for all characters in the table. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#26
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Hi again,
Am Thu, 31 Mar 2016 20:11:44 +0200 schrieb Claus Busch: please look again in OneDrive for "16000 - FY2017 Store Planning File_V1.3" I improved again the function that you don't need the IF statement. Now you can use myTime(...) for all characters in the table. I changed the formula in week 8 again. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#27
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Thank you Claus!
I'll have a look next week. Have a great weekend. |
#28
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Hello Claus,
I've taken your code and updated my UDF but have found that it isn't rectifying the issue but I wonder if making this change will only affect the formula being used going forward and not update previous issues. For example, I've gone into an existing file and updated the mytime UDF to the following: http://pastebin.com/sPPbkiTt When I update the UDF, save and exit from Visual Basic I can still see schedules showing in future weeks although there is no detailed schedule to support it. Now what I've found is that when these "ghost" schedules showing up in future summaries they will go away when you select the cell, click F2 to access the formula and then hit enter. So it would seem as though the mytime formula works, it just needs to be forcibly recalculated. I don't know if this is happening based on any of the following: * all of the sheets and cells in question are "locked" via the protection set up in excel to avoid the user from modifying the formulas. Based on this I've developed the following work around. When you have to print the summary I use a macro that effectively filters out those staff who do not have any hours for the week and only shows those who have a detailed schedule. What I've found is that the filtering process I have in this macro effectively clears these "ghost" summary schedules and prints correctly.. Here is the macro I have set up to do this: http://pastebin.com/Azp2v9QJ It would appear that the filtering mechanism is effectively "forcing" the mytime formulas to recalculate. I get a similar result when I 1) unprotect the sheet, 2) select a cell with the mytime formula, 3) hit F2 to select the cell's mytime formula and then 4) click enter to exit from the formula. Again, seems to be forcing the mytime formula to complete. I have a work around which is OK for when you print the summary schedule but when you look at future weeks those "ghost" summary schedules are still there. Very odd one, not sure what attributes to this but wanted to feedback in case you had any other ideas. Thanks again, Daniel |
#29
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logical based Horizontal Lookup
Hi Daniel,
Am Tue, 12 Apr 2016 04:22:53 -0700 (PDT) schrieb Daniel Petta: I've taken your code and updated my UDF but have found that it isn't rectifying the issue but I wonder if making this change will only affect the formula being used going forward and not update previous issues. please look he https://onedrive.live.com/redir?resi...=folder%2cxlsm for "16000 - FY2017 Store Planning File_V1.4" and download the file because macros are disabled in OneDrive. You don't need the small characters into the code. With UCase all Characters will be handled capitals as well as small characters. There is no need to change anything into the code. You have to change the formulas in your cells to =myTime(C64:AX64)and then copy down. In week 53 and week 1 I already changed the formulas. Please have a look. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vertical and horizontal lookup? | Excel Worksheet Functions | |||
Horizontal and Vertical lookup | Excel Worksheet Functions | |||
lookup horizontal... | Excel Worksheet Functions | |||
Sumproduct and horizontal lookup | Excel Discussion (Misc queries) | |||
Need help with a dynamic lookup and logical formula | Excel Worksheet Functions |