Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: up date list (excel )

    Welcome to the Lounge

    This can probably be done with VLOOKUP but we need a bit more information to assist. How is the list set out and what is the criteria used to determine who the next in line will be.

  2. #2
    New Lounger
    Join Date
    Mar 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    up date list (excel )

    I have a list with name of works of difference ranks in my office, what I need is a formula / code what will see the next person in line to act in a position if the person is send on holiday or leave the job.
    There are about eight difference position to be fill if the person at the top is send on Holiday or leave the Job can I have the excel do it automatically for me
    I have not make up the list as yet but here are some of the category of the difference Positions
    E.g Manager, Asst. Manager, Line foreman , Asst. line foreman , Store keeper, Building Foreman . Asst. Building, Asst. Store keeper, etc. The person how is (most qualify) should act first
    Can you say if there is a formula that you can put in your worksheet so that if you delete some thing from one line in your worksheet it will go to a next worksheet in the last line under the last information of that worksheet

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: up date list (excel )

    Solid,
    Here's a sheet that works with what you asked, I think.

    As Tony requested, a bit more information might be helpful. In my chart, I used a "MIN" formula to find the lowest ranking person (#1 being the current boss). Then I used that with VLOOKUP to select the name of the lowest numbered person. Also, in this chart, you need only delete the rank number, not the name. That way when the person returns, you just pop the correct number back in!

    Does this help?

    Errol

  4. #4
    New Lounger
    Join Date
    Mar 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: up date list (excel )

    I have send you a example of the worksheet I have not put in all the ranks,

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: up date list (excel )

    I am envisioning a table of ranks and using VLOOKUP to find the highest ranking person not on vacation, but I am not clear how you want to be setup.

    I am still not clear on what you what the spreadsheet to do exactly. How will you indicate that people are on vacation? How are they ranked for in charge? Could you provide us with a few details on how you want the spreadsheet to work and what type of info you will be providing to excel.

  6. #6
    New Lounger
    Join Date
    Mar 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: up date list (excel )

    Thank you For Trying
    solid

  7. #7
    New Lounger
    Join Date
    Mar 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: up date list (excel )

    Please keep on helping me

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: up date list (excel )

    I am still not sure of your goals. How about this example. In a spreadsheet, add a list of names (in increasing "rank) and columns for End and start "leaves":
    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center valign=bottom>1</td><td valign=bottom>Name</td><td valign=bottom>LeaveStart</td><td valign=bottom>LeaveEnd</td><td align=center valign=bottom>2</td><td valign=bottom>Jean-Luc Picard</td><td align=right valign=bottom>12/25/2003</td><td valign=bottom> </td><td align=center valign=bottom>3</td><td valign=bottom>William T. Riker</td><td align=right valign=bottom>02/15/2004</td><td align=right valign=bottom>05/15/2004</td><td align=center valign=bottom>4</td><td valign=bottom>Data</td><td align=right valign=bottom>03/26/2004</td><td align=right valign=bottom>04/11/2004</td><td align=center valign=bottom>5</td><td valign=bottom>Geordi LaForge</td><td align=right valign=bottom>04/25/2004</td><td valign=bottom> </td><td align=center valign=bottom>6</td><td valign=bottom>Deanna Troi</td><td valign=bottom> </td><td valign=bottom> </td><td align=center valign=bottom>7</td><td valign=bottom>Beverly Crusher</td><td valign=bottom> </td><td valign=bottom> </td></table>

    Note:
    Picard is on extended leave which started last year and there is no return date
    Riker started Leave in Feb and won't be back until May
    Data is on leave 3/26 and won't return until 4/11
    LaForge starts leave 4/25 with no return date.
    Troi and Crusher have no leave scheduled.

    The ARRAY formula (confirm with ctrl-shift-enter) will give the name of "Who's in Charge" based on this "Chain of Command":
    =INDEX(A1:A7,MIN(IF((1-ISNUMBER(B2:B7)*(TODAY()>=B2:B7))+(ISNUMBER(C2:C7) *(TODAY()>=C2:C7))+(ISBLANK(B2:B7)*ISBLANK(C2:C7)) ,ROW(A2:A7))))

    Which is "Geordi LaForge" on "Today" (3/29/2004). On 4/11/2004 "Data" will be in Charge until Riker returns on 5/15. If Picard returns and Today is ">=End date" then he will be in Charge until he leaves again.

    If this is not what you are after, could you provide more details, I am not sure what to make of "example spreadsheet"

    Steve
    PS
    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>is no need to respond to multiple people, one response is sufficient. This is especially true if you are attaching identical spreadsheets.

  9. #9
    New Lounger
    Join Date
    Mar 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: up date list (excel )

    <P ID="nt"><font size=-1>(No Text)</font>

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: up date list (excel )

    All of these are 1 line and ARRAY (confirm with ctrl-shift-enter)
    In G2 is the manager:
    =INDEX(B1:B7,MIN(IF((1-ISNUMBER(C2:C7)*(TODAY()>=C2:C7))+(ISNUMBER(D27)*(TODAY()>=D27))+(ISBLANK(C2:C7)*ISBLANK(D27)),ROW(B2:B7))))

    In H2 is the Asst Manager:
    =INDEX(B1:B7,SMALL(IF((1-ISNUMBER(C2:C7)*(TODAY()>=C2:C7))+(ISNUMBER(D27)*(TODAY()>=D27))+(ISBLANK(C2:C7)*ISBLANK(D27)),ROW(B2:B7)),2))

    In I2 is the line foreman:
    =INDEX(B1:B7,SMALL(IF((1-ISNUMBER(C2:C7)*(TODAY()>=C2:C7))+(ISNUMBER(D27)*(TODAY()>=D27))+(ISBLANK(C2:C7)*ISBLANK(D27)),ROW(B2:B7)),3))

    To add more just change the last number to get the next smallest row number. [You could use the same formula with a "1" for the manager since Small(x,1) = min(x)]
    Steve
    PS Deanna and Beverly are women, It is actually "Counselor Troi" and "Dr. Beverly Crusher". They are all characters in Star Trek: The Next Generation

  11. #11
    New Lounger
    Join Date
    Mar 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thank You!

    Let me take the time off in saying thanks for all that was done on working out the formula for me, it 's working fine, thanks again

  12. #12
    New Lounger
    Join Date
    Mar 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    up date list (excel )

    One of the things I over look in the formula is that , Some persons are already acting and can't double act in the company because their are not qualify to act in that position ,I need to stop those persons from going up the ladder . How can i start over the formula under the last person that is acting in the same column so that the persons under them can continue to act if some one is send on leave

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: up date list (excel )

    I don't understand your spreadsheet, could you elaborate?

    People should not be "dbl acting" if they get moved up the people below will move up in the position.

    If some people are not qualified to move up, how do you distinguish them? It is not just a simple go thru the list:
    My method ranks everyone from top to bottom. If a position has someone "on leave" then everyone moves up one one notch. If that is not how it is done, I will need more details on how people move up, when they stay, etc as you have a lot more things to check for.

    You might need a table that has each name and their "rank" for each position and then take the highest rank for each position. You will also need to have some indication of which position takes precedence if someone is next in line for more than 1 position.

    Steve

  14. #14
    New Lounger
    Join Date
    Mar 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    (excel )

    I have send you some more up date in the attachment

    Thank you for trying with me

    Can you send me detail of the formula that is work so good so that i can learn some thing from it

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: (excel )

    To be honest, I am still not completely clear on the setup and what you want. Why are some of the rows without names, my setup would require you to put these ranks on "leave" so they are not picked as the ranking officer.

    It seems to me, from your latest spreadsheet, that you just have 2 lists (each one can be done with a separate use of the formulas I gave earlier). One would be the chain of command for rthe People in B4:B16 and the 2nd list would be the people in B17: B36.

    The people in B17:B36 would never go into the list from B14:B16 and the people in B4:B16 would never be in the lower list.

    Regarding the fomula:
    1) it contains an array formula so it works with multiple cells.
    For more on arrays see:
    Chip Pearson and Bob Umlas which are great resources for understanding them
    Break up the formula into "pieces" for better "digesting"
    =INDEX(B1:B7,SMALL(IF((1-ISNUMBER(C2:C7)*(TODAY()>=C2:C7))+(ISNUMBER(D27)*(TODAY()>=D27))+(ISBLANK(C2:C7)*ISBLANK(D27)),ROW(B2:B7)),2))

    ISNUMBER(C2:C7)
    will give an array of 6 values of True or False depending on whether each of the items in C2:C7 is a number

    TODAY()>=C2:C7)
    will give an array of 6 values of True or False depending on whether Today's date is greater or equal to each of the items in C2:C7

    ISNUMBER(C2:C7)*(TODAY()>=C2:C7)
    Is essentially an "AND" of the first 2 conditions, both must be TRUE for the combination to be TRUE. (since you mult it deals with 1 or 0, 1 is true, 0 is false, 1*1 = 1 =TRUE but if either is 0: 0*1=1*0=0*0 = 0 = False)

    (1-ISNUMBER(C2:C7)*(TODAY()>=C2:C7))
    Subtract the 6 values from above each from 1, gives an array NOTting the values, True becomes false, false becomes true

    This part essentially gets you TRUE if based on the start date you are not on leave (ie your start date is blank or is >today)

    (ISNUMBER(D27)*(TODAY()>=D27))
    Is similar to the above for column C. It gives TRUE to the people who's leaves have ended before today

    (1-ISNUMBER(C2:C7)*(TODAY()>=C2:C7))+(ISNUMBER(D27)*(TODAY()>=D27))
    Just l like Multi is AND, an OR is done with summing. This gets a TRUE if Either the (start date is blank OR the start is >today) or the enddate is before today.

    ISBLANK(C2:C7)*ISBLANK(D27)
    Checks to see if both Start and end dates are blank.This was neccessary in case a leave was left with an end date, but no startdate.

    This is the combined criteria:
    (1-ISNUMBER(C2:C7)*(TODAY()>=C2:C7))+(ISNUMBER(D27)*(TODAY()>=D27))+(ISBLANK(C2:C7)*ISBLANK(D27))

    This gets a TRUE if Either the (start date is blank OR the start is >today) or (the enddate is before today) or (both the dates are blank)
    Now what the formula uses is an IF:
    IF((1-ISNUMBER(C2:C7)*(TODAY()>=C2:C7))+(ISNUMBER(D27)*(TODAY()>=D27))+(ISBLANK(C2:C7)*ISBLANK(D27)),ROW(B2:B7))
    If that above is true we get an array of 6 values which are the row numbers. Thus we have up to 6 rowvalues (if no one is on leave)

    SMALL(IF((1-ISNUMBER(C2:C7)*(TODAY()>=C2:C7))+(ISNUMBER(D27)*(TODAY()>=D27))+(ISBLANK(C2:C7)*ISBLANK(D27)),ROW(B2:B7)),2)

    This looks at the array of the 6 values and gets the 2nd smallest (this if for 2nd place. for "top dog" I used:
    Min(IF((1-ISNUMBER(C2:C7)*(TODAY()>=C2:C7))+(ISNUMBER(D27)*(TODAY()>=D27))+(ISBLANK(C2:C7)*ISBLANK(D27)),ROW(B2:B7)))
    Though you could have used:
    SMALL(IF((1-ISNUMBER(C2:C7)*(TODAY()>=C2:C7))+(ISNUMBER(D27)*(TODAY()>=D27))+(ISBLANK(C2:C7)*ISBLANK(D27)),ROW(B2:B7)),1)

    Each formula for the rank uses a different "place" (the number at the end) to mark, 1st, 2nd, 3rd, etc.

    This is 1 value that gives the row number for the ranking you have chosen (1-whatever)

    The final step is to look thru the list of names with the INDEX function to get the name from that row:
    =INDEX(B1:B7,SMALL(IF((1-ISNUMBER(C2:C7)*(TODAY()>=C2:C7))+(ISNUMBER(D27)*(TODAY()>=D27))+(ISBLANK(C2:C7)*ISBLANK(D27)),ROW(B2:B7)),2))

    Hope this helps,

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •