Forcing a cell to have a value if another cell has a value

[bh24524]

Hi, I use a spreadsheet at work to track employee attendance. The particular bit of information I am concerned with is Mandatory Overtime (Mando). in Cell J8, it has a space for them to enter the time that Mando was posted. Below it in J9, is the cell that they should be putting a time that Mando was lifted. It either needs a specific time or has to say "Not Lifted" The problem is a lot of the times, J9 is being left blank so that there is only a post time. This shouldn't be happening so I am wondering if there is a way to force something to be entered in J9 if J8 has content?

 

[godsaaint]

Is it always going to be J8/J9, all employees have a different sheet?

 

[bh24524]

Is it always going to be J8/J9, all employees have a different sheet?

Yeah. Mando applies to all employees, it just has a post time so they know that anyone having left before it was posted is under a different assessment. But there must always be a Lift time or it must say “Not Lifted”. Employees are listed all on the same sheet.

 

[godsaaint]

and how do you determine the specific time it was lifted? Is it the same time the user wrote something in cell J8?

So for example, it's currently 19:30, and the user inputs in J8 the time the mando was posted, let's say 17:00, so then in J9 it should change from "Not lifted" to 19:30?

 

[bh24524]

Okay here is a better explanation with an example. The attendance sheets are custom toward different shifts. Let's use Perishable 1st shift as an example. That shift runs from 7AM to 3PM. If the workload seems like it will be very big, they will call mando at a specific time of day and then lift it once they can estimate when it will all be completed. So for example, they might call Mando at Noon but then it isn't lifted until 5PM, so workers have to stay 2 hours beyond their shift time to get it done. So for that day in example, mando was posted at 12PM and Lifted at 5PM for that whole shift. In some cases, the workload will be so immense that they will not lift Mando, hence the option of writing "Not lifted in J9. Does that help?

 

[godsaaint]

Ok, if I understand correctly, you can insert this formula in J9: =IF(J8="","","Not lifted")

The thing is, if the user is also the one going to be the one to input the time it was lifted in J9, then after he does he is going to overwrite the formula. So when someone else puts another mando time another day, J9 is not going to say anything.

 

[bh24524]

Actually thats not quite what I'm looking for. That I know how to do myself. Basically, I want to see if it will force someone to put a value in J9 if there is a value in J8. I have a special data validation set up in the same sheet for another column. In that column, it will only allow them to select a list of options. If they try to type something else, it won't let them. I have heard that something like this is possible with macros but I am not too familiar with those. If someone would happen to know how to use one and possibly guide me to set it up?

 

[bh24524]

Hi I am bumping this. Does anyone know of an answer to this?