auto populate original staff name

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
Max HerseyResidential SupervisorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
I don't understand 2. Please try again.
Martin LissOlder than dirtCommented:
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 SupervisorAuthor 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.
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Martin LissOlder than dirtCommented:
Give this a try.
29130731.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Max HerseyResidential SupervisorAuthor Commented:
the hours thing works great! but I couldnt get the original staff name to auto populate
Martin LissOlder than dirtCommented:
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 SupervisorAuthor 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 dirtCommented:
Other than locking, is the workbook doing what you want?
Max HerseyResidential SupervisorAuthor Commented:
it doesnt seem to be populating the original staff name once the three variables are put in.
Martin LissOlder than dirtCommented:
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 SupervisorAuthor 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 SupervisorAuthor 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 dirtCommented:
See if this is better. The Original Staff is auto populated as soon as the Replacement Staff is chosen.
29130731a.xlsm
Max HerseyResidential SupervisorAuthor 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 dirtCommented:
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 SupervisorAuthor 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 dirtCommented:
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 SupervisorAuthor 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 dirtCommented:
Sure, I’ll do that tomorrow.
Max HerseyResidential SupervisorAuthor Commented:
Awesome Thanks! I have a few other ideas to try out as well.
Martin LissOlder than dirtCommented:
Did it now.
29130731c.xlsm
Max HerseyResidential SupervisorAuthor 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 dirtCommented:
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 SupervisorAuthor Commented:
Can you add the updated file?
Martin LissOlder than dirtCommented:
Oh, thought I did.
29130731d.xlsm
Max HerseyResidential SupervisorAuthor 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 dirtCommented:
No, so please describe the situation, what happened and what should have happened.
Max HerseyResidential SupervisorAuthor 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 dirtCommented:
In a new row when columns A to D are filled in, what if anything should appear in E?
Max HerseyResidential SupervisorAuthor 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 dirtCommented:
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 SupervisorAuthor 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 dirtCommented:
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 SupervisorAuthor 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 dirtCommented:
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 SupervisorAuthor 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 dirtCommented:
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 SupervisorAuthor Commented:
I think we should have all the cells filled out before it locks the row (A to J)
Martin LissOlder than dirtCommented:
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 SupervisorAuthor Commented:
all of them except for the notify column. its once I pick the replacement name that it disappears
Martin LissOlder than dirtCommented:
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 SupervisorAuthor 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 dirtCommented:
There was a non-obvious typo in cell L11 which read "4pm-9-pm" instead of "4pm-9pm".
29130731g.xlsm
Max HerseyResidential SupervisorAuthor Commented:
Oh haha well that was silly of me.
Martin LissOlder than dirtCommented:
No problem.
Max HerseyResidential SupervisorAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.