I have a sheet which i modified to make a shift rota. It finds the most recent date an individual has been on duty, then makes a table showing the individuals in reverse order according to those dates.
It has worked very well, but the shift has got bigger and when i add in the extra personnel, the formula has stopped working
I have attached the working version, and the now non working version to see if anyone could point me in the direction of the issue / problem?
Please put both into one workbook for simplicity, then I'll have a look for you.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks very much, i've attached as requested.
You need to update the ROW reference:
=IF(D4="","",INDEX($E$17:$E$27,SMALL(IF($B$17:$B$27=D4,ROW($1:$11)),COUNTIF(D$4:D4,D4))))&" "&IF(D4="","",INDEX($F$17:$F$27,SMALL(IF($B$17:$B$27=D4,ROW($1:$11)),COUNTIF(D$4:D4,D4))))
Forgive me if i'm not seeing something obvious, but that now replaces the #N/S error with a #VALUE error.
There are no errors here.
You might need to enter the formula with CTRL+SHIFT+ENTER before copying down - did you do that? I didn't have to as I have Excel 365.
That's fantastic thank you. Bit of a head scratcher as i can't find a single difference between mine and yours, but i'll use the one you sent back to me to base the new version on. Thanks again for your help.
I highlighted the difference in RED: yours had $1:$9, mine has $1:$11 (in TWO places).
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks