1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by stepheny2k2, Mar 26, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. stepheny2k2

    stepheny2k2 Thread Starter

    Joined:
    Jul 8, 2006
    Messages:
    48
    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? :confused:
     
  2. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Can you attach a sample file (remove sensitive data with dummy data)?
     
  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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?
     
  4. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I agree.
     
  5. stepheny2k2

    stepheny2k2 Thread Starter

    Joined:
    Jul 8, 2006
    Messages:
    48
    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.(y)

    But hope it makes things a little clearer.

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

    Attached Files:

  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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 ;) )?
     

    Attached Files:

  7. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Why does the user want the time displayed again in Column P?
     
  8. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    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
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/812936

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice