Need help with with timesheet i am creating

Dona88

New Member
Joined
Mar 31, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I am creating a timesheet on excel currently.

The sheet consist of different cells,start finish,total hours etc, they are all fine.

I have a drop down menu as one of the cells, which consist of working day, annual leave and public holiday, what I am trying to figure out, is I need to link the drop down option with a certain hour when choosing it,so for example if I choose annual leave as an option on the drop menu in column C, I need the column for total hours column G to automatically change to show 07:12 but I dont how to do it, I know how to add a conditional format to colour code it but how to link a specific value to a drop down option.

Any help would be great.

Thanks
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

HighAndWilder

Board Regular
Joined
Nov 4, 2006
Messages
173
Office Version
  1. 365
Platform
  1. Windows
If you create a Worksheet_Change procedure in the Worksheet code module as below then you will be able to check
the value selected using the drop down menu and then do something. Example assumes that the drop down menu is in cell L2.

If you need help in filling in the code then please be specific on what you need.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$L$2" Then
        
        ' Do something here.
    
          MsgBox Target.Address
    
    End If
    
End Sub
 

Dona88

New Member
Joined
Mar 31, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Yes I do need help with the code, what do you need from me. I have tried to attach a photo but saying to large
 

HighAndWilder

Board Regular
Joined
Nov 4, 2006
Messages
173
Office Version
  1. 365
Platform
  1. Windows
For each of the options on the drop down menu what do you want to happen?

If you zoom the screen in and use the snipping tool to grab an image we can zoom it out.
 

Dona88

New Member
Joined
Mar 31, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

There you go, managed to add a photo for you.

So as you can see the drop menu is on column c, what I what to happen is if I choose AL on the drop down menu, is there a way you can automatically have column g (total hours column) to change to 07:12 (7hours 12mins) and al2 automatically change to 03:36 (3 hours 36 minutes). I have tried different formulas, I tried conditional format it but none of that seems to work.
So each option needs to show this on column G -

WD - 00:00, as this will be manually added by the collegues
NWD - 00:00
AL - 07:12
AL2 - 03:36
TIL - 00:00
SL - 07:12
SL2 - 03:36
PH - 07:12

If this is possible, could you please advise how it can be done? So I can formulate on my sheet.

Thank you
 

Attachments

  • Timesheet capture.PNG
    Timesheet capture.PNG
    43 KB · Views: 5

HighAndWilder

Board Regular
Joined
Nov 4, 2006
Messages
173
Office Version
  1. 365
Platform
  1. Windows
Try this:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim arrTimes() As String
Dim arrOptions() As String

    arrOptions = Split("NWD,AL,AL2,TIL,SL,SL2,PH", ",")
    arrTimes = Split("00:00,07:12,03:36,00:00,07:12,03:36,07:12", ",")
    
    If Target.Column = 3 Then
        
        Range("G" & Target.Row) = arrTimes(Application.WorksheetFunction.Match(Target.Value, arrOptions, 0) - 1)
    
    End If
    
End Sub
 

Dona88

New Member
Joined
Mar 31, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
So it worked,thank you but an error message came up when I went to choose WD asan option.

Please see attached image.

Should I maybe add WD in the list on the code as 00:00?
 

Attachments

  • Error Message.PNG
    Error Message.PNG
    56.4 KB · Views: 3
  • Error.PNG
    Error.PNG
    33.8 KB · Views: 3

HighAndWilder

Board Regular
Joined
Nov 4, 2006
Messages
173
Office Version
  1. 365
Platform
  1. Windows
Does you list of options on the drop down menu match the list in the code and in the same order?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,583
Messages
5,625,622
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top