[stepheny2k2]

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?

[computerman29642]

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

[Zack Barresse]

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?

[computerman29642]

Originally Posted by Zack Barresse

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.

[stepheny2k2]

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.

[Zack Barresse]

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 )?

[computerman29642]

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

[MRdNk]

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.

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