up date list (excel )

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Thank you For Trying
solid

Reply | Quote

Thank you For Trying
solid

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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”:

	A	B	C
1	Name	LeaveStart	LeaveEnd
2	Jean-Luc Picard	12/25/2003
3	William T. Riker	02/15/2004	05/15/2004
4	Data	03/26/2004	04/11/2004
5	Geordi LaForge	04/25/2004
6	Deanna Troi
7	Beverly Crusher

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
is no need to respond to multiple people, one response is sufficient. This is especially true if you are attaching identical spreadsheets.

Reply | Quote

(No Text)

Reply | Quote

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(D2:D7)*(TODAY()>=D2:D7))+(ISBLANK(C2:C7)*ISBLANK(D2:D7)),ROW(B2:B7))))

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

In I2 is the line foreman:
=INDEX(B1:B7,SMALL(IF((1-ISNUMBER(C2:C7)*(TODAY()>=C2:C7))+(ISNUMBER(D2:D7)*(TODAY()>=D2:D7))+(ISBLANK(C2:C7)*ISBLANK(D2:D7)),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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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(D2:D7)*(TODAY()>=D2:D7))+(ISBLANK(C2:C7)*ISBLANK(D2:D7)),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(D2:D7)*(TODAY()>=D2:D7))
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(D2:D7)*(TODAY()>=D2:D7))
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(D2:D7)
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(D2:D7)*(TODAY()>=D2:D7))+(ISBLANK(C2:C7)*ISBLANK(D2:D7))

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(D2:D7)*(TODAY()>=D2:D7))+(ISBLANK(C2:C7)*ISBLANK(D2:D7)),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(D2:D7)*(TODAY()>=D2:D7))+(ISBLANK(C2:C7)*ISBLANK(D2:D7)),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(D2:D7)*(TODAY()>=D2:D7))+(ISBLANK(C2:C7)*ISBLANK(D2:D7)),ROW(B2:B7)))
Though you could have used:
SMALL(IF((1-ISNUMBER(C2:C7)*(TODAY()>=C2:C7))+(ISNUMBER(D2:D7)*(TODAY()>=D2:D7))+(ISBLANK(C2:C7)*ISBLANK(D2:D7)),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(D2:D7)*(TODAY()>=D2:D7))+(ISBLANK(C2:C7)*ISBLANK(D2:D7)),ROW(B2:B7)),2))

Hope this helps,

Steve

Reply | Quote

please look at the example on the attachment

Reply | Quote

In the “actual column” put in the formula in reference to col c so it always stays that person. In F2 enter
=$C2
Copy this to the other locations (I4, K6, etc). Then use the array formula in the other cells. You don’t need the complicated (Array) formula to get the “real” ACFO, only the “acting ACFO”

Note:
you must send the “blank rows” (rows 3,5,8,9) “on leave” or you will choose them (and get zeroes, since there is no name in Col C) in the acting person. Just put a date in the commencement date (col D) and leave the “resumption date” (Col E) blank for these rows with no people (or delete the rows).

Each formula should increase the “Small value” by 1 (acting ACFO = 1, acting DFO is 2, acting ADFO is 3 and 4, etc)

Hope this helps,
Steve

Reply | Quote

please look at the attachment

Reply | Quote

I am confused by your setup. some of the “true columns” have “acting calculations” (the megaformula) while some just at at column C(which is what I expect. You still haven’t fixed the formulas that I mentioned in an earlier post and you still are getting “0”s since you did not send the “blank” people on leave (or do you want to fix this in the formula?)

The acting ACFO should use “Small 1” not “small 2” when the ACFO is not on leave he should be displayed as the acting ACFO, not a 0. You also seem to still have missed many of the “small numbers”. Each number from 1 – whatever should be used each row should be the next number.

I am not sure what col M is supposed to represent since it has no heading.

I am also confused as to why to put it into this big array. Why not just use 1 column for the “acting person” the “actual” is already listed in col C, why extend it out, just not just 1 column next to the leave columns for acting?

Steve

Reply | Quote

I am confused by your setup. some of the “true columns” have “acting calculations” (the megaformula) while some just at at column C(which is what I expect. You still haven’t fixed the formulas that I mentioned in an earlier post and you still are getting “0”s since you did not send the “blank” people on leave (or do you want to fix this in the formula?)

The acting ACFO should use “Small 1” not “small 2” when the ACFO is not on leave he should be displayed as the acting ACFO, not a 0. You also seem to still have missed many of the “small numbers”. Each number from 1 – whatever should be used each row should be the next number.

I am not sure what col M is supposed to represent since it has no heading.

I am also confused as to why to put it into this big array. Why not just use 1 column for the “acting person” the “actual” is already listed in col C, why extend it out, just not just 1 column next to the leave columns for acting?

Steve

Reply | Quote

Look at this attachment. It has them all in 1 column. I added a new column to get the “small number” I modified it a little to get rid of the blank entries. My original formulas assumed that names/people would be in every row.

Steve

Reply | Quote

Look at this attachment. It has them all in 1 column. I added a new column to get the “small number” I modified it a little to get rid of the blank entries. My original formulas assumed that names/people would be in every row.

Steve

Reply | Quote

please look at the attachment

Reply | Quote

In the “actual column” put in the formula in reference to col c so it always stays that person. In F2 enter
=$C2
Copy this to the other locations (I4, K6, etc). Then use the array formula in the other cells. You don’t need the complicated (Array) formula to get the “real” ACFO, only the “acting ACFO”

Note:
you must send the “blank rows” (rows 3,5,8,9) “on leave” or you will choose them (and get zeroes, since there is no name in Col C) in the acting person. Just put a date in the commencement date (col D) and leave the “resumption date” (Col E) blank for these rows with no people (or delete the rows).

Each formula should increase the “Small value” by 1 (acting ACFO = 1, acting DFO is 2, acting ADFO is 3 and 4, etc)

Hope this helps,
Steve

Reply | Quote

please look at the example on the attachment

Reply | Quote

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(D2:D7)*(TODAY()>=D2:D7))+(ISBLANK(C2:C7)*ISBLANK(D2:D7)),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(D2:D7)*(TODAY()>=D2:D7))
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(D2:D7)*(TODAY()>=D2:D7))
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(D2:D7)
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(D2:D7)*(TODAY()>=D2:D7))+(ISBLANK(C2:C7)*ISBLANK(D2:D7))

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(D2:D7)*(TODAY()>=D2:D7))+(ISBLANK(C2:C7)*ISBLANK(D2:D7)),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(D2:D7)*(TODAY()>=D2:D7))+(ISBLANK(C2:C7)*ISBLANK(D2:D7)),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(D2:D7)*(TODAY()>=D2:D7))+(ISBLANK(C2:C7)*ISBLANK(D2:D7)),ROW(B2:B7)))
Though you could have used:
SMALL(IF((1-ISNUMBER(C2:C7)*(TODAY()>=C2:C7))+(ISNUMBER(D2:D7)*(TODAY()>=D2:D7))+(ISBLANK(C2:C7)*ISBLANK(D2:D7)),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(D2:D7)*(TODAY()>=D2:D7))+(ISBLANK(C2:C7)*ISBLANK(D2:D7)),ROW(B2:B7)),2))

Hope this helps,

Steve

Reply | Quote

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

Reply | Quote

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(D2:D7)*(TODAY()>=D2:D7))+(ISBLANK(C2:C7)*ISBLANK(D2:D7)),ROW(B2:B7))))

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

In I2 is the line foreman:
=INDEX(B1:B7,SMALL(IF((1-ISNUMBER(C2:C7)*(TODAY()>=C2:C7))+(ISNUMBER(D2:D7)*(TODAY()>=D2:D7))+(ISBLANK(C2:C7)*ISBLANK(D2:D7)),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

Reply | Quote

(No Text)

Reply | Quote

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”:

	A	B	C
1	Name	LeaveStart	LeaveEnd
2	Jean-Luc Picard	12/25/2003
3	William T. Riker	02/15/2004	05/15/2004
4	Data	03/26/2004	04/11/2004
5	Geordi LaForge	04/25/2004
6	Deanna Troi
7	Beverly Crusher

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
is no need to respond to multiple people, one response is sufficient. This is especially true if you are attaching identical spreadsheets.

Reply | Quote

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

Reply | Quote

Please keep on helping me

Reply | Quote

Please keep on helping me

Reply | Quote