Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108132

Re: Logical based Horizontal Lookup

From isabelle <i@v.invalid>
Newsgroups microsoft.public.excel.programming
Subject Re: Logical based Horizontal Lookup
Date 2015-10-28 13:21 -0400
Organization Aioe.org NNTP Server
Message-ID <n0r061$6pu$1@speranza.aioe.org> (permalink)
References <f856d7a2-0a29-458f-968b-f96688885938@googlegroups.com> <n0qvlj$50c$2@speranza.aioe.org>

Show all headers | View raw

please look here:

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",COLUMN($C2:$AX2)),1),""))&":"&IF(ISEVEN(IFERROR(SMALL(IF($C2:$AX2="x",COLUMN($C2:$AX2)),1),"")),"30","00")&"
>
> -
> "&INDEX($A$1:$AX$1,IFERROR(LARGE(IF($C2:$AX2="x",COLUMN($C2:$AX2)),1)+1,""))&":"&IF(ISEVEN(IFERROR(LARGE(IF($C2:$AX2="x",COLUMN($C2:$AX2)),1),"")),"00","30")
>
>
> isabelle
>
> Le 2015-10-28 10:41, dvpetta@gmail.com 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/gsfp5tt3uwoh620/schedule%20template.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!
>>

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Logical based Horizontal Lookup dvpetta@gmail.com - 2015-10-28 07:41 -0700
  Re: Logical based Horizontal Lookup isabelle <i@v.invalid> - 2015-10-28 13:12 -0400
    Re: Logical based Horizontal Lookup isabelle <i@v.invalid> - 2015-10-28 13:21 -0400
      Re: Logical based Horizontal Lookup dvpetta@gmail.com - 2015-10-28 10:29 -0700
  Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2015-10-28 18:46 +0100
    Re: Logical based Horizontal Lookup isabelle <i@v.invalid> - 2015-10-28 14:18 -0400
      Re: Logical based Horizontal Lookup GS <gs@v.invalid> - 2015-10-28 14:39 -0400
        Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2015-10-28 21:23 +0100
          Re: Logical based Horizontal Lookup GS <gs@v.invalid> - 2015-10-28 17:38 -0400
    Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2015-10-28 20:51 +0100
      Re: Logical based Horizontal Lookup dvpetta@gmail.com - 2015-10-29 11:19 -0700
        Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2015-10-29 19:33 +0100
          Re: Logical based Horizontal Lookup dvpetta@gmail.com - 2015-10-29 11:42 -0700
    Re: Logical based Horizontal Lookup "Robert Crandal" <noreply2me@yahoo.com> - 2015-11-12 00:14 -0700
      Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2015-11-12 08:18 +0100
  Re: Logical based Horizontal Lookup dvpetta@gmail.com - 2015-10-29 08:57 -0700
  Re: Logical based Horizontal Lookup dvpetta@gmail.com - 2016-03-15 10:08 -0700
    Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2016-03-16 11:12 +0100
      Re: Logical based Horizontal Lookup Daniel Petta <dvpetta@gmail.com> - 2016-03-16 04:44 -0700
        Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2016-03-16 13:12 +0100
          Re: Logical based Horizontal Lookup Daniel Petta <dvpetta@gmail.com> - 2016-03-16 06:15 -0700
            Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2016-03-16 14:25 +0100
              Re: Logical based Horizontal Lookup Daniel Petta <dvpetta@gmail.com> - 2016-03-31 09:56 -0700
                Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2016-03-31 19:45 +0200
                Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2016-03-31 20:11 +0200
                Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2016-03-31 20:12 +0200
                Re: Logical based Horizontal Lookup Daniel Petta <dvpetta@gmail.com> - 2016-04-01 09:14 -0700
                Re: Logical based Horizontal Lookup Daniel Petta <dvpetta@gmail.com> - 2016-04-12 04:22 -0700
                Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2016-04-12 14:18 +0200

csiph-web