# Thread: up date list (excel )

1. ## 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. ## 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. ## 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. ## Re: up date list (excel )

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

5. ## 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. ## Re: up date list (excel )

Thank you For Trying
solid

8. ## Re: up date list (excel )

<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. ## Re: up date list (excel )

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

10. ## 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. ## 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. ## 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. ## Re: up date list (excel )

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. ## (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. ## 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 Last

#### Posting Permissions

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