Working with someone elses workbook to alter for same purpose but larger area of data

SCHEDULE FOLSOM.xlsx

Okay, I am new to this company and they gave me an excel work book with only one spread sheet. The spread sheet calculates and determines employee job id, job classification and total hours worked. The workbook stopped working for them and they asked me to take a look. It has been a good 20 years since I have done this kind of error tracking and fixing but I am still pretty keen about it all. So, the problem is that off to the side of the actual cells data is entered into there are the cells containing the circular references so part of the data is automatically populated.

For example:
Column C is "Job ID" the values are 1=server, 2=Hostess, 3=busser, 4=chef, 5=kitchen, 6=manager and 7=admin
Column D is "Job" the values are server, hostess, busser, chef, kitchen, manager and admin
Column E is "Begin" the time the shift is to start
Column F is "End" the time the shift ends
All of the above when static data is entered populate the rest of the table(rows across) for a time frame of 9:30am to 2:00am

The formula/function that was entered and I am trying to repair is:
column AW[code] =IF(CA8*BL8=0,,$D8)
CA8[code] =IF($D8+0.0208333333333333<=$F8,$C8,)
BL8[code] =IF($D8>=$E8,$C8,)

$E8 represents "Begin"
$C8 represents "Job ID"
$F8 represents "End"

What is obviously happening is that when they entered more columns and rows to expand the times from an hourly basis to a half hourly basis and to add more employee names AND added 2 more worksheets that would also populate from this worksheet the circular references got confused. Now I am trying to connect it all back together without much success.

What I need to happen is when a begin time and end time is entered in columns E & F it grabs Job Type along with the color of the cell it is stored in and paste it across the row.

Example: 9:30am-1:30pm is entered for Mindy she is a server "job type" with "job id" 1 this info is static but not the times. This then populates the row from 9:30am to 1:30pm with a continuous "Server" in a blue cell.

Hope this makes some sense and that there is someone or someones here to help guide me to a solution. If the workbook is needed I can email it to you.

Thanks for reading! Lillian

its a little hard to make out what could be happening. would it be possibel to upload the workbook with any sensitive data removed?

thx. a few questions on shift schedule sheet...

job ID C10 downwards refers to job ID 0. That ID is not in the lookup list

off to the side of the data entry area, in a range of hidden columns, Y8 has this formula...=IF(BC8*AN8=0,,$D8)
an if statement has 3 conditions...if(criteria,what-to-do-if-true,what-to-do-if-false) that formula has no "what-to-do-if-true" condition

the referemces in the above formula references AN8, which contains =IF(#REF!>=$E8,$C8,), and BC8 contains =IF(#REF!+0.0208333333333333<=$F8,$C8,). in both instances the formulae only contain 2 of the 3 conditions.

im trying to follow the logic for the #REF, but while im doing that, maybe you could look at the other questions I asked?