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 list of protocols?

Discussion in 'Business Applications' started by number, Jan 18, 2006.

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

    number Thread Starter

    Joined:
    Oct 15, 2003
    Messages:
    1,053
    hi,

    I'm using excell to list daily protocols with date, person and object. I was wondering if anybody knows about a function which automatically writes down the date and protocol number chronologically, so that it automatically changes the date the next day.
    I hope I have been clear!
    thank you!
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,896
    The Date can be entered automatically using your computer system date with the =Now() function. So if you put your Protocol in column A and =Now() in column B which is hidden. You could then have some Visual Basic on the Application Close event that pastes the value of column B in to Column C. You can't just use now() as it changes each day.
     
  3. number

    number Thread Starter

    Joined:
    Oct 15, 2003
    Messages:
    1,053
    thank you OBP but you'll have to explane it step by step cause i'm not sure of how to do it...
    i've attached the example of what i'm talking about : in the first 2 columns on the right there is the protocol number and date, they both should proceed automatically when i'm writing for example the name of a customer. of course i have to be able to open more protocol numbers in the same date.
    thanks a lot!
     

    Attached Files:

    • prot.JPG
      prot.JPG
      File size:
      185.5 KB
      Views:
      97
  4. number

    number Thread Starter

    Joined:
    Oct 15, 2003
    Messages:
    1,053
    any luck? i know it's difficult though.
    thanks
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,896
    Ideally this should be in an Access Table with autonumber incrementing the number for you.
    In Excel you may be able to use a User Input Form or Visual Basic to do the same thing.
     
  6. number

    number Thread Starter

    Joined:
    Oct 15, 2003
    Messages:
    1,053
    thanks OBP, unfortunately I'm not fluent with visual basic, do you know how to do it?_ thanks!
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,896
    If you have Access, I would rather create you a nice Access database which could output the results to Excel if you wanted it to.
    In the mean time I will ask one of the other Excel Specialists to have a look to see if they can do it using formulae.
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,896
    Number, can't you just create the Protocol numbers in sequence in advance?
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    The number can be incrimented very easily actually. Juse =A1+1, then format as "000" or for however many leading zero's you want. If you need it as text, you can use a formula such as this ...

    =TEXT(A1+1,"000")

    This will show 002, 003, 004, etc, but it will be text and not a number.

    As far as the date goes, you can auto-enter it into a cell by clicking Ctrl + : (semi-colon). You can't use a formula for this as date functions are volatile and would update everyday.

    Am I understanding this correctly?
     
  10. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Perhaps you mean that when you make an entry in column C, you want the date (today, fixed) to appear in column B and the number (sequential) to appear in column A.

    If so, you will need some event code -- something like:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Selection.Cells.Count > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    If Target.Offset(, -1) = "" Then
    Target.Offset(, -1) = Date
    Target.Offset(, -2) = WorksheetFunction.Max(Range("A:A")) + 1
    End If
    End Sub


    This needs to go in the worksheet module -- to get to it, rightclick the sheet tab & select "View Code".

    A sample workbook is attached. When you make an entry in C9, A9 and B9 should fill in.

    HTH,
    Andy
     

    Attached Files:

  11. number

    number Thread Starter

    Joined:
    Oct 15, 2003
    Messages:
    1,053
    THANKS A LOT BOMB#21! that's perfect!
    one last question, how can I import that macro in my protocol? where is it saved?
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,896
    Number, as Bomb says right click on the sheet name at the bottom of his sheet and select view code. Copy the code, go to your worksheet and repeat the right click on the sheet name and select view code and Paste the code in the same place on your sheet's Module as it was on his.
     
  13. number

    number Thread Starter

    Joined:
    Oct 15, 2003
    Messages:
    1,053
    thanks guys it worked, actually when I imported the code at first it did work, the only problem was that it started from # 01 while (and I forgotten to say this) the protocols # started from 054 .
    Now for some reasons I cant even make the code work. I'm attaching my document, could you guys tell me how to adapt the code to my excel document?
    thanks a lot!
     

    Attached Files:

  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,896
    number, the reason it doesn't work with your worksheet is because your worksheet does not have values in column "A", so when it increments it starts from zero. Change your Column "A" to values and you should find it works OK.
    see attached worksheet.
     

    Attached Files:

  15. number

    number Thread Starter

    Joined:
    Oct 15, 2003
    Messages:
    1,053
    thanks, it works now. the very last thing, my protocols have to be listed (as you can see in the example i've attached) with 001, 002, so no 63 but 063, i know its simple but I'm not able to adjust it in the code. Can you help me?
    thanks a lot again!
     

    Attached Files:

    • ex.xls
      File size:
      14.5 KB
      Views:
      135
  16. 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/435115

  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