Excel 2003: automatically changing a cell's contents based on keyword entered

I have built a simple timesheet for someone in my dept, which includes amongst other things:

- hours worked over a month,
- how many hours owed/extra worked
- a cumulative of this figure over the months.

The requestor wants to be able to type a letter such as 'e' to represent a 07:30hr shift. So they type 'e' and the cell contents change to 07:30. There are about 6 different codes.

Normally a Vlookup would work EXCEPT you can't change the contents of the cell your are working upon.....cicrular referencing!

I could see two ways forward: either a function that allows the above

OR (perhaps more favourably)

leaving the lookups until the hour calculations at the base of the form. i.e Instead of a simple Sum for the hours worked that month, it could read the values in the range, look them up from a table(vlookup), and then sum the looked up figures to give the answer in the 'hours worked' cell.

I vaguely remember this being possible from my university days but can't remember how.

Can anyone help me?

 

Can you attach a sample file (remove sensitive data with dummy data)?

 

Hi there,

I recommend conditional formatting from the sounds of it. But I hesitate leaving this alone with that sole recommendation after reading your post. I am curious to look at your workbook, it sounds like there may be other things we can do with it, i.e. reduce some inefficiencies.

Would you agree, Chris?

 

I agree.

 

Okay I have attached a sample of what I have done.

I have to stress this was just a prototype to test the feasability of what she was asking for and whether it matched her expectations.

But hope it makes things a little clearer.

Anything else you need or require further explanation of let me know.

 

Perhaps an in-cell drop down for the times, with them already populated in a pre-defined list? I've made many time sheets, and that is what I do 90+% of the time.

Take a look at the changed sheet, there is a list, then look at E4. Would that help, or is that what you're looking for? Also, is the time just time checked-in? What exactly are the codes for and how are they supposed to work (not necessarily how implimented, leave that to us )?

 

Why does the user want the time displayed again in Column P?

 

It is possible to do what you originally asked for, but it seems slightly overkill, as the user can just type in 7:30 or select it from a list like you currently have.

However, here is a starting point, for your original question, this can be extended to include a list on your second lookup sheet:
NB. Put the code behind Sheet1.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As String
    If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
        If Target.Value = "e" Then
            Target.FormulaR1C1 = "7:30"
        End If
    End If

End Sub