Can it be done???

[richard hales]

Morning Everyone
My question is can it be done, I have 2 work books. Work book1 named roster and work book2 named Site1. Work book1 roster feeds information into work book2, now work book2 contains a number of work sheets such as roster, weekly hours, Holiday planner and weekly timesheets. In work book1 you would put the employee’s working day such as 06:00 to 14:00 and the total of hours worked 8 this information is then linked into work book2 roster which then works out the basic day hours which is 8 this is then linked into the employee’s weekly timesheet. Now if the employee was to do extra hours (overtime) in work book1 roster you would put 06:00 to 18:00 with working hours 8, why 8? because the employees original shift was 8 hours for that shift. This information (06:00 to 18:00 with working hours 8) is linked into work book2 roster and then linked into weekly hours which then works out the overtime hours now any overtime done by an employee must have a reason why entered on they weekly timesheet such as covering holidays or sickness so my question is, is there any way of hyper linking the cell which contains the overtime hours into the weekly timesheet making the user of work book1+2 to put a reason why this employee as done overtime. I was thinking of a warning message box which would pop up when the overtime cell contain a value and maybe a hyperlink in the warning message linking then to the time sheet of the employee am looking for something which will force the user to complete the reason why overtime was done if not completed the user can’t go on with any work on these work books or another work sheet called overtime or something like that but there needs to be something in place to tell the user that an employee as done overtime or the user could ignore the part of the time sheet which requires a reason why there is overtime.
And to add a little twist as well once the reason for overtime as been entered it would return the user back to work book1.

 

[gardnertoo]

If you were to handle the data entry using a User Form (this involves VBA macros), it could be built in such a way that the data entrant cannot exit until all the required blocks are filled in. If you don't want to go the macro route, you could use conditional formatting to highlight the "reason for OT" cell in a different color as soon as overtime hours are recorded. This will not force the user to make the entry, but it would at least alert them that it needs to be done. I use this technique on the timesheets at my workplace now, and it seems to work well.

 

[richard hales]

Hi gardnertoo
First thing many thanks for replying. This technique on the timesheets sounds like the job for the job. Just one thing I’ve haven’t use conditional formatting to highlight a cell before could you give me any pointers or an example please. So I understand this when a value (hours) show up in the overtime cell the cell for "reason for OT" cell will change colour and if nothing is in overtime cell it doesn’t change colour??

 

[gardnertoo]

So I understand this when a value (hours) show up in the overtime cell the cell for "reason for OT" cell will change colour and if nothing is in overtime cell it doesn’t change colour??

Exactly. Suppose that cell C4 is the cell where overtime hours appear. This could either be a calculated value (actual hours - normal hours) or a direct entry (today I worked x number of OT hours). In either case, the same technique will work. Select the cell in which the "OT Reason" should go. I'll use cell E4 in this example, change it as suits your actual spreadsheet layout. From the Excel menu select Format, then Conditional Formatting... A dialog box will appear. In the first box, chose Formula Is. In the second box, enter a formula like this, using the correct cell references:

=AND($C$4>0,$E$4=0)

Then in the box below choose what formatting you want applied if the condition is true.

 

[richard hales]

Hi gardnertoo
That little trick works a treat my team supervisor's love it. Just one question i have 250 cell to do i've mastered how to do one cell at a time is there away to do a block of them at once.

 

[dannac]

I think you could have highlighted the whole block you want to conditional format, before entering the formula.

or

Now that you hav one cell done, double click the icon painter on the toolbar, and copy to all cells you want formatted.

 

[gardnertoo]

What Dan said, but with a catch: your conditional formatting has to use relative refernces so it can "follow" the appropriate cells down the page. What I mean is the conditional formatting for a row 8 cell probably needs to look at A8 and E8, not $A$4 and $E$4. Just get rid of the dollar signs in the conditional formatting of the first cell, then do the format painting.

 

[richard hales]

Hi gardnertoo
Many thanks to you and dannac.