auto populate original staff name

Max Hersey
Max Hersey used Ask the Experts™
on
1) Can the original staff column of the AFT tab auto populate based on the name that is already on the house tab for that specific date and shift time? Rather than it being a drop down.
2) Can the over hours column on the AFT tab auto-populate the total hours (that are calculated on the hours tab) for the replacement staff for the given week that the shift falls into?
Schedule.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I don't understand 2. Please try again.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Are you saying that if the date were 1/1 and the Replacement Staff was Employee10 that H2 on the AFL tab should be 56?
Max HerseyResidential Supervisor

Author

Commented:
Yes that is exactly it. but now that I am thinking about it, could we add the extra hours that would be added from the shift being offered. so for example, if the shift being offered was a 7-3 shift (8 hours) then the over hours tab would be 56 + 8 = 64.
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Give this a try.
29130731.xlsm
Max HerseyResidential Supervisor

Author

Commented:
the hours thing works great! but I couldnt get the original staff name to auto populate
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Unless I don't understand what you mean, I think it already does that.

Before:
2018-12-27_16-28-32.pngAfter:
2018-12-27_16-30-10.png
Max HerseyResidential Supervisor

Author

Commented:
so on january 1st 2019, the staff name ffor the 7 to 3 shift on the pinegarry tab is employee28. So what I was hoping we could do is have the original staff auto populate the name for the position. So in this case it would be employee28 in the original staff column. then, when the shift is confirmed, it would lock that row on the AFT tab so that when the replacement staff name replaces the original staff name, it wont change the original staff name on the AFT tab because the row is locked.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Other than locking, is the workbook doing what you want?
Max HerseyResidential Supervisor

Author

Commented:
it doesnt seem to be populating the original staff name once the three variables are put in.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I don’t have the code in front of me right now but I believe it does that when Confirm is changed to “Yes”.
Max HerseyResidential Supervisor

Author

Commented:
oh ok I was hoping the original staff would populate on the AFT tab once the three variables are filled in on the AFT tab. then once the lock happens it wont change the original name on the aft tab but it will change the name on the house tab.
Max HerseyResidential Supervisor

Author

Commented:
I just want the AFT tab  columns to auto populate as much as possible so that there is less work for my staff to do. Plus its a good control.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
See if this is better. The Original Staff is auto populated as soon as the Replacement Staff is chosen.
29130731a.xlsm
Max HerseyResidential Supervisor

Author

Commented:
It still doesn't seem to be filling in the original staff column. So for example, if the shift is a 6am to 9am on Jan 1, 2019 at maison pacific the original staff name on the AFT tab should auto populate to employee1.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
All this time I thought that the only time that the original staff should auto populate is if the Replacement Staff was changed. I understand now it should also happen if the shift changes. Should it also change if the Shift Date or House changes?
Max HerseyResidential Supervisor

Author

Commented:
It should always auto-populate based on the shift date, house, and shift time that is chosen on the AFT tab (Column B, C, and D). Based on what is in those three cells, it will pull the name from the house tabs. so on jan 1, 2019 at maison pacific for the 6 to 9am shift, "Employee1" would auto fill in the original staff cell because that is the name on the maison pacific tab on jan 1 for the 6 to 9am shift.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
I believe this does what you want. I needed to change the values in the AFL sheet ranges L16 and L18, removing the underscore, so that they matched the sheet names.
29130731b.xlsm
Max HerseyResidential Supervisor

Author

Commented:
Yes it works well now! Is there a way for it to say open if there is not a name in the position already? For example, On Jan. 11th there is no one working the 8 to 4 shift at francis miller. Because there is not a name in that position could it say open? This is important because sometimes we have to schedule additional shifts that arent even filled.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Sure, I’ll do that tomorrow.
Max HerseyResidential Supervisor

Author

Commented:
Awesome Thanks! I have a few other ideas to try out as well.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Did it now.
29130731c.xlsm
Max HerseyResidential Supervisor

Author

Commented:
It seems to change for the 8 to 4 shifts but not the other shifts that should be open. For example, if you pick a shift that is filled like the 12 to 8 (employee30) and then click the 6 to 9 or 7 to 3 or 4 to 9 then it stays as employee30. Also, I tried to confirm the shift and it locked the row properly but the replacement staff name did not seem to fill in properly. I had the jan 11 francis miller 12 to 8 confirmed with employee11 as the replacement staff. It removed employee30 on the francis miller tab but left the position blank.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Ok, it wasn't as easy as I thought but I think it's fixed now.

I made two additional changes:
  • I sorted the list of employees that are shown in the userform. It looks wrong now but I believe when there are real names it will be in alphabetical order. If you don't want it that way then please let me know and I can easily change it back to the way it was.
  • I changed the color of the locked cells. If you don't like it then go to the 'Home' tab at the top of Excel, select the little dropdown button in the lower right-hand portion of the 'Styles' section and choose one that you like better. You could also tell me to just change it back to the way it was.
Max HerseyResidential Supervisor

Author

Commented:
Can you add the updated file?
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Oh, thought I did.
29130731d.xlsm
Max HerseyResidential Supervisor

Author

Commented:
Yes this works good! I noticed that a random number goes into the original staff cell if the shift does not appear. Is this supposed to happen?
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
No, so please describe the situation, what happened and what should have happened.
Max HerseyResidential Supervisor

Author

Commented:
i tried filling in a new row on the AFT tab and when u fill in the A, B, and C column, the E column generates a random number. I also just noticed that the original staff name disappeared when I enter the replacement staff name, the original staff name is removed. Also, when the replacement staff was employee11 the hourse column was -16. not too sure why this is happening
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In a new row when columns A to D are filled in, what if anything should appear in E?
Max HerseyResidential Supervisor

Author

Commented:
when D is filled in it gives either open or the employee name. but when D isnt filled in yet it gives a random number.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I've fixed he random number ib column E but in a new row where just A to D are filled in do you want to see "Open"?
Max HerseyResidential Supervisor

Author

Commented:
Yea as long as that shift is not already filled. If it is filled then it should show the employee name
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this. BTW, make note of this somewhere but shouldn't there be some restriction on when a row can be locked? Currently it can be locked without error if just the house has a value and everything else is blank.
29130731e.xlsm
Max HerseyResidential Supervisor

Author

Commented:
everything else seems to be fixed except for when we pick the replacement staff, the original staff name disappears on the AFT tab
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I don't see that happening, I'm looking at the AFT sheet and the original staff is 26 and the replacement staff is 11. I choose 30 for the replacement staff from the userform and now original staff is 11 and replacement staff is 30. Isn't that correct?
Max HerseyResidential Supervisor

Author

Commented:
hmmm. maybe try filling out a second row because that is what happens when I fill one out for the first time. Also, I agree that there should be regulations so that it can be locked until all cells in that row r filled out
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Also, I agree that there should be regulations so that it can be locked until all cells in that row r filled out
For the next or subsequent change please describe what cells are required to be filled prior to locking.
Max HerseyResidential Supervisor

Author

Commented:
I think we should have all the cells filled out before it locks the row (A to J)
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
maybe try filling out a second row because that is what happens when I fill one out for the first time.
In the new row what columns are filled in when this happens?
Max HerseyResidential Supervisor

Author

Commented:
all of them except for the notify column. its once I pick the replacement name that it disappears
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
I think I fixed it. The code was saying that whenever G is changed that E becomes the existing value in G. In a new line G is blank and so  E was made blank. I changed that to say that if G is blank, don't update E.
29130731f.xlsm
Max HerseyResidential Supervisor

Author

Commented:
yea i think that did fix it! now it is just the hour column that is still not working for me.
Screenshot--1-.png
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
There was a non-obvious typo in cell L11 which read "4pm-9-pm" instead of "4pm-9pm".
29130731g.xlsm
Max HerseyResidential Supervisor

Author

Commented:
Oh haha well that was silly of me.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
No problem.
Max HerseyResidential Supervisor

Author

Commented:
I have a few other things that I would like to add to this workbook but I am going to make it a new question.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial