Rank formula to carry over from worksheet to worksheet?

[Scrapnforfun]

Hi,
I'm trying to establish a new rank per each worksheet based on the least amount of time worked. The person with the least amount of time goes to the top of the list. I wanted this list to change with each day in the calendar based on the previous day.

On April 1, if Sue, Larry, Jane, Sally, Martha and Joyce start out the day each without any hours worked. Sue works 1 hour, 10 am to 11 am, and then Larry works 12 pm to 4 pm. Sue has only an hour worked and Larry has 4 hours worked. Everyone else did not work for April 1.

I want to rank in order by hours, person with the least amount of hours on top, to appear on the next worksheet. If you'll notice on April 2, Larry got moved to the bottom of the list and Sue is second from the bottom because she worked less than Larry did. By Sue and Larry working, that pushed up Jane into the 1st spot, Sally, Martha and Joyce to follow in 2nd, 3rd, and 4th.

I would like the pages to automatically populate this ranking on each day of the week. I have it automatically populating for April 1 and April 2 but can't seem to go beyond that.

I've also put in VBA macro for sheetoffset function, but was just playing around with it, not really knowing what I was doing.

Can someone help me get the rest of the worksheets to populate for the month automatically with the employees' names and the least amount of hours based on the previous day's work? I would like to do this for each month, too.

Thanks

[Pete_UK]

I think you need the formula to calculate the rank in column J (this will determine the next day's rank after the hours have been input). Then the ranking in column A can just be static, i.e. 1 to 6, as you have shown in the sheet for 2nd April. The VLOOKUP for the names will have to change to an INDEX/MATCH, but essentially for a particular day you want to find a match from column A with column J in the previous sheet, and then bring the corresponding name from column B. Your formula would need to convert the sheet name to a proper date so that you can determine the previous sheet in order to get the data from there.

Hope this helps.

Pete

[Scrapnforfun]

Oh, boy, Did I tell you that I was new to this? I thought in order to use "rank" it needed to be in the furthest left column. I did start off with 1, 2, 3, etc., and once you input the time it changes the order on April 1 and then automatically on April 2 with the correct order to start a new day. I have never used index/match before. I guess, short story, is there something you can point me to to get me where I need to go? I've basically been learning off youtube.
Thanks so much for your quick reply.
Lin

[Pete_UK]

Okay, I'm in the process of setting this up for you.

Do you work on Saturdays and Sundays? As it will look back at the previous (dated) sheet, I need to know whether to avoid weekends in the formulae.

Also, the file you attached was an .xls file (i.e. XL2003 and earlier). Do you need it to be compatible with earlier versions?

Pete

[Scrapnforfun]

Hi, Pete,
We don't work weekends. I am on 2010 so I don't need it earlier. I don't even know how I got that version. Are you serious? You're going to set it up. That's amazing. I'm happy with whatever help you can give me.
Thanks so much!
Lin

[Pete_UK]

I've set this up in the attached file, and checked it out by inserting some new sheets and putting some dummy data in.

In cell A1 of the first sheet (and indeed on all the other sheets) I have this formula:

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

which is a way of getting the sheet name into the sheet. In B2 (on all sheets) I have this formula:

=DATE("20"&RIGHT(A1,2),LEFT(A1,FIND("-",A1)-1),SUBSTITUTE(MID(A1,FIND("-",A1)+1,2),"-",""))

which converts the sheet name in cell A1 to a date that Excel can work with. I have just put the (fixed) numbers 1 to 6 down column A for the rank, and used column J for the next-day's rank with this formula in J4:

=RANK(I4,I$4:I$21,1)

which is then copied into J7, J10 etc. This looks at the hours that each person has worked, and ranks them, so those with fewer hours have the lowest number rank (as before). In the first sheet (4-1-16) the names are typed into column B but in subsequent sheets the names are determined by the rank in column J of the previous sheet. To achieve this I have used cell J1 on all but the first sheet to evaluate the previous date, using this formula:

=TEXT(B$2-IF(WEEKDAY(B$2,2)=1,3,1),"m-d-yy")

and then the formula in B4 is:

=INDEX(INDIRECT("'"&J$1&"'!B4:B21"),MATCH(A4,INDIRECT("'"&J$1&"'!J4:J21"),0))

which is then copied into B7, B10, and so on. This might look a bit daunting, but basically it finds where 1 (given in A4) is in column J of the previous sheet (using the MATCH part of the formula), and then brings the name from column B of that row in the previous sheet. The formula in B7 will be looking for the value of 2 (i.e. what is in A7), and so on.

When you add some data to columns D to G of a sheet, the ranking in column J will automatically adjust - this will determine the order that the names appear in the following sheet. I've added a few sheets for testing purposes, but you will need to do this quite often as the file gets used. You can select the last tab and do CTRL-drag on this to create a new sheet. If you do it with 4-11-16 then the new sheet will be named 4-11-16 (2), and you will need to rename this to the next working date in sequence (double-click the tab name, then edit it to 4-12-16 {and remove the space from the end}, then press Enter). If you have put any data into columns D to G of what is now the previous sheet, you will need to remove it from the newer sheet. Obviously, you should avoid renaming the sheet to a date which is a weekend.

Anyway, that's all there is to it.

Hope this helps.

Pete

[Scrapnforfun]

Pete, It works. It's exactly how I wanted it. How do you know how to use the formulas you did? Is there some place I can go to learn how these formulas work? Like, when you use "'", I have no idea what that means. Is there a cheat sheet somewhere? I've been getting by looking at YouTube videos, and at work they have supplied me with Excel beginners and intermediate training. I haven't been given the opportunity to create formulas as extensive as you have here. I am taking the advanced class in June and am hoping it will focus more on formulas. I just can only seem to get so far. Anywhere you can direct me on formulas would be great.

Thanks again. I really appreciate it. You don't know how happy you made me. I was agonizing over this for weeks and had to finally consult help. Thanks again
Lin

[Pete_UK]

Hi Lin,

glad it works for you. As for formula knowledge, I've been helping out in these Forums (and others) for over 10 years, and although I thought I knew lot when I started, I have certainly picked up a lot more knowledge from other contributors and from reading other threads. This is a very good place to learn about Excel, even if you don't have a particular problem to be solved, so keep coming back when you have some spare time and I'm sure you will pick a few things up.

I've been thinking about how you should handle holidays with the file, as it is set up to expect sheets for Monday to Friday. There are two approaches you could take:

a. suppose Monday 11th April was a holiday, and you had a sheet for Tuesday 12th April - the Tuesday sheet would expect to get data from the Monday sheet, but you can see that if there is no data on that Monday sheet (i.e. hours worked) then the ranking in column J is the same as the initial ranking, so the Tuesday sheet would effectively get the data from the previous sheet (Friday 8th April). Thus you could have a sheet for every day (excluding weekends), but leave a holiday sheet blank. You could put HOLIDAY in big red letters in cell D2, for example, so it is obvious why there is no data in it.

b. If you did not want to clutter the file up with blank sheets for holidays, then you could omit the sheet for Monday 11th April. But, the Tuesday sheet would be trying to get data from that Monday sheet, so you would need to manually change the name of the previous sheet in cell J1. In this case you would just type '4-8-16 in cell J1 of the Tuesday sheet, so that it would get the data from the previous Friday sheet. You need to type an apostrophe before the previous date (which keeps it as a text value), otherwise Excel will convert it to a proper date, and that will mess up the formulae. Obviously, you should NOT copy that sheet forward to be used for the next day, as it will have been modified.

Anyway, something to think about.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[Pete_UK]

Hi Lin,

thanks for the rep.

I thought of something else that you might find useful - recording the cumulative hours worked for each person.

In the attached file I have used column K for this, and in the first sheet (only) I have this formula in K4:

=I4*24

This is formatted as Number with 2dp, and then copied into K7, K10 etc.

In K4 in the second (and all subsequent sheets) I have used this formula:

=I4*24+IFERROR(VLOOKUP(B4,INDIRECT("'"&$J$1&"'!B4:K21"),10,0),0)

again, formatted as Number with 2dp, and then copied into K7, K10 etc. This gets the appropriate hours from the previous sheet and adds it on to the hours in the current sheet - maybe you can make use of this (you could do another ranking on this column). Note that these are decimal hours, so 1:30 will show as 1.5.

Hope this helps.

Pete

[Scrapnforfun]

I love that idea, Pete!
I'm going to work it in for sure.

Regarding holidays, I'm also going to keep the sheet in as a blank sheet, taking your advice, and writing in red "Holiday" because we do need to keep track of that also.

Thanks so much for thinking outside the box for me
L

[Scrapnforfun]

Hi, Pete, again
I love your cumulative idea which reminded me of something I forgot in this whole scenario. Is it possible to have the cumulative amount at the end of a week to then determine the rank order starting on a Monday, new week? Instead of having the rank continue throughout the month continually, can every Monday a new rank start based on the total of the previous week? I'm thinking this will totally change what you have done so far as far as formulas now (I hope not too much). Also, when you go to the next month, can it take the data above into the next month in a new workbook to set the rank order?

I guess in simple terms:
1) rank for each day until Friday.
2) rank cumulative for the week to then determine the rank for the following Monday
3) rank to be carried over to the next month in a new workbook

I have been trying to break down your formulas and learn from them. Thanks so much for your explanations up above. What is "2dp"?

If this is too much now that I've changed the plan a bit, please let me know. I know you're helping and I appreciate that you have other things to do. Please accept my thanks in advance for all you have done already.

Lin

[Pete_UK]

Hi Lin,

I've made a few changes to the attached file, to give you something like you asked for. I've use column L in all sheets to give a cumulative rank (i.e. a rank on the cumulative hours), using this formula in L4:

=RANK(K4,K$4:K$21,1)

which is then copied into L7, L10, and so on. Once you have done this on one sheet, you can just copy that column and paste it into column L of all the other sheets in turn.

Then on the second and subsequent sheets (not the first sheet), I've changed the formula in B4 to this (changes in red):

=INDEX(INDIRECT("'"&J$1&"'!B4:B21"),MATCH(A4,INDIRECT("'"&J$1&IF(WEEKDAY($B$2,2)=1,"'!L4:L21","'!J4:J21")),0))

and then copied this into B7, B10 etc. The bit that I've added checks to see if the date in B2 is a Monday, and if so it will take the ranking from column L of the previous sheet, otherwise it will take it from column J as before. This is not quite what you are asking for, as you wanted the Monday positions to be based on the cumulative hours for the previous week, whereas this will be based on the cumulative hours in the month so far. The approach I've taken is a lot easier to achieve with the existing formulae.

It is much more difficult to get the starting positions of one month from the previous month if that is in a different file. You can see that a number of the formulae use the INDIRECT function, which allows us to "calculate" the appropriate cell references as a string and Excel is able to convert them into cell references that it understands. However, the INDIRECT function does not work with closed workbooks, so this would mean that you would need to have the previous month's file open at the same time as the file that you are working on, otherwise the formulae will produce errors. This in turn would mean that if you were working on a file for November, say, then you would need to have the October file open, and that would need to have the September file open, and that would require August's file to be open, and so on all the way back to the first file that you created - it would become unmanageable with so many open files.

Consequently, I think you will have to regard a month's file as complete in itself. You will have a starting sheet which will be the first working day of that month (not necessarily the 1st or second of the month, if those dates occur at a weekend), and in that you will have to type the names in yourself, as we have done so for 4-1-16. You will have about 20 other sheets for the working days in that month which will have the formulae in column B to determine the name (as well as the previous sheet name in cell J1). At the start of each new month you will have to type the names into column B manually, based on the cumulative rank in the last day of the previous month.

Hope this helps.

Pete

[Scrapnforfun]

Thanks so much, Pete. I opened it quickly and it looks great. I haven't tried it yet. Unfortunately I've been busy at work all day today, and now will be in training for four days next week. Guess what in? Access. Oh, boy!
I will definitely get to it next Friday and try and make sense of all your corrections. I think you were right, by frequenting the messages here, I'm starting to understand it better.
I will definitely give you another great rep. I actually tried, but it said something to the effect of I needed to comment on another's posting before I could on yours again (something like that.)
Once again, though, I appreciate you taking the time to help me work through this with your explanations.
Lin

[Scrapnforfun]

Hi, Pete,
I'm not sure if you will receive this, or even remember thisworksheet, but I wanted to thank you for doing all this. It's been a real big help. I did have a question, though, and maybe it's not something that can be fixed, but when I use multiple worksheets, the third worksheet tab and the rank for number 6, in the cumulative column the time turns from 0.00 to 0.01 even without data in there and then a few more tabs over it turns to 0.05. Do you know why this would be happening?

I am referring to this formula box:

"In K4 in the second (and all subsequent sheets) I have used this formula:

=I4*24+IFERROR(VLOOKUP(B4,INDIRECT("'"&$J$1&"'!B4:K21"),10,0),0)"

Thanks!!
Lin

[Pete_UK]

Hi Lin,

yes, I remember working on this workbook.

The problem relates to the formula that you have in column I of each worksheet. The total hours for the first person (i.e. in cell I4) is this:

=SUM(H4:H6)+0.000000001

and subsequent formulae are similar, but the small number being added on has one fewer zero each time. I assumed at the time that this was your attempt to introduce a tie-break, and as it seemed to be working I didn't question it.

The problem is that you are adding a small amount of time onto each, maybe only fractions of a second, and if you do this a number of times those seconds will become minutes and start to show in the display.

Hope this helps.

Pete

[Scrapnforfun]

Perfect, Pete!
That's what I thought it was, also, for the tie-break.
Thanks for getting back to me.
Lin