Help with auto-populating text

I am trying to create an overtime chart for work that will automatically populate text in the cells adjacent to the cell where text is entered. For example, if I enter B in cell A1, I want A to automatically enter in cell B1 and C to automatically enter into B2. Is this possible? Also, can I keep the sheets separate? So it pulls the information from sheet 2 whenever the text is entered in sheet 1? So in the attached workbook it would be referring to the names in the Employee column on sheet 2. An additional question I have is would it be possible to also have a calendar on a 3rd sheet that inserts the name on the corresponding date you enter when you make an entry on sheet 1?
Attachment 629118Attachment 629119

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

It's not clear what you are looking for since your spreadsheet (sheet1) is empty. Give some examples of what you would enter and what you would expect to appear automatically (and why).
What's the difference between an operator, wind up and let off?

Okay, almost there. Question:
I notice Eric appears on here twice. Will names appear on the list more than once? Your employee covering sheet doesn't specify what piece of equipment to pull the name from. This isn't an issue if the names appear in a single column (as they do on your example) and don't repeat.

Okay, I am giving 2 solutions.
Solution 1 assumes that the number of employees for each machine is consistent (in this case 4 for each machine) Solution 1 results in a longer formula.

Solution 2 does not assume that so requires some manual effort initially. I set up 2 blank spaces between each machine and then copied the first name on the list above to the first blank, and the last name on the list below to the second blank. Then I added a blank space " " after these names so they are (to Excel) different than the original names. (So "John" versus "John "). Solution 2 results in a fairly short formula.

Solution 1: In C2

Formula:

=IFERROR(IF(INDEX(Sheet2!$C$3:$C$23, MATCH(B2, Sheet2!$C$3:$C$23,0)-1)=0, INDEX(Sheet2!$C$3:$C$23, MATCH(B2, Sheet2!$C$3:$C$23,0)+3),INDEX(Sheet2!$C$3:$C$23, MATCH(B2, Sheet2!$C$3:$C$23,0)-1)),"")

In C3

Formula:

=IFERROR(IF(INDEX(Sheet2!$C$3:$C$23, MATCH(B2, Sheet2!$C$3:$C$23,0)+1)=0, INDEX(Sheet2!$C$3:$C$23, MATCH(B2, Sheet2!$C$3:$C$23,0)-3),INDEX(Sheet2!$C$3:$C$23, MATCH(B2, Sheet2!$C$3:$C$23,0)+1)),"")

Then copy that pair of cells and copy them down the page.

[B][/Solution 2:B] In Solution 2, the MATCHs will ignore the names with the spaces after them so things are much simplier. In C2

Formula:

=IFERROR(INDEX(Sheet2!$D$3:$D$26, MATCH(B2, Sheet2!$D$3:$D$26,0)-1),"")

In C3
=IFERROR(INDEX(Sheet2!$D$3:$D$26, MATCH(B2, Sheet2!$D$3:$D$26,0)+1),"")

See attached workbook

RE: the calendar. Yes, it would be (maybe) possible. The solution would depend on how the calendar is set up. On any given date, there are multiple shifts with each shift having multiple machines with someone assigned to each machine. How would you set that up?

For the calendar the following conditions would need to be met:
1. Only 18 employees per day. After 18 we start a waiting list.
2. An entry may only be made if the name above or below (employee covering) is not already on that day from it's respective group (machine).

There are certain days that are exempt from these conditions so we would remove them from those days to allow unlimited entries. The calendar does not need to have any other information other than the name. It is simply used to let employees know that their day off has been approved.

By the way, Thank you VERY much for your help! This is definitely more than I would have been able to figure out on my own. I can certainly see an Excel class coming up for myself in the near future.

So I've been working on it and I still have along way to go but I've made a few changes. Again, thanks a ton! I'm figuring out a lot by working with what you gave me. I attached what I have so far

If I understand correctly then columns A:B on the calendar sheet could be filled with date and name using:

Formula:

=IFERROR(INDEX('OT Sheet'!A$2:A$42,AGGREGATE(15,6,(ROW(A$2:A$42)-ROW(A$1))/(ISEVEN(ROW(A$2:A$42)))/('OT Sheet'!A$2:A$42<>""),ROWS(A$1:A1))),"")

Note that for this proposal to work dates will are individually placed on the OT Sheet (i.e. Brian Sullivan 7/1-7/3)
Column C could be filled with the number of employees whom will be off that day using: =IF(A2="","",COUNTIFS(A$2:A2,A2))
If this isn't what you want, please include expected results on the calendar sheet so that we can attempt to replicate them using formulas/code.
Let us know if you have any questions.

This proposal employs a redesigned overtime signoff form in columns J:T. The formulas are the same formulas as in the original.
Calendar dates in rows 3 and 22 are dates custom formatted "d"
The formula that populates the calendar is: =IFERROR(INDEX('OT Sheet'!$L$2:$L$42,AGGREGATE(15,6,(ROW($A$2:$A$42)-ROW($A$1))/('OT Sheet'!$J$2:$J$42=B$3),ROWS($A$1:$A1))),"")
Note that the calendar is only filled through July 13th.
Let us know if you have any questions.