+ Reply to Thread
Results 1 to 9 of 9

Creating a project tracker

  1. #1
    Forum Contributor sorensjp's Avatar
    Join Date
    12-23-2015
    Location
    Yokosuka, JP
    MS-Off Ver
    2007 and 2016
    Posts
    188

    Creating a project tracker

    Hello all,

    -- Advice request

    I'm looking to create a Excel file to track problems at work. In my mind I see using a user form to add/edit/save the data on each problem. Once saved to populate a printable dashboard. I would like to maintain a copy of the information initially and subsequently entered. But I want to only show the most current (via a time stamp or something) data on the printable dashboard.

    I'm in the US Navy and we have what is call 8 O'clock reports. They are basically short description of any problems to include status updates (estimated time of repair, point of contact...etc).

    I'm hoping from some suggestions on this project. Ideally if there is a template to start from would be ideal.

    Thank you for the advice and direction,

    Jim

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,239

    Re: Creating a project tracker

    Hi Jim

    I can visualise a rough outline of what you are looking for.
    I suggest
    - Form1 for initial problem logging, and Form2 for subsequent updates
    - a sheet containing list of logged problems
    - a sheet containing list of follow up updates

    For the dashboard, you are going to want the different types of problems analysed

    Problems (unfortunately) are generally repetitive and so we should be able to list them so that they can be selected via a dropdown instead of having to be totally re-typed with only the additional specific details needing entry.

    Each problem will have a series of follow up actions and subsequent updates
    - what type of information should be captured?
    - personnel
    - action
    - date & time
    - sign off
    etc

    I have attached a workbook to give you idea of the above (no forms created yet!)
    - Mock_up will become Form1 (drop-downs in cells B5,B6 & B8 - pulling detains from sheets Location,ProblemType and Personnel)
    - the other 2 sheets will contain Logged Problems and List of Follow-ups

    It would be easier and quicker if you could sketch out something along the above lines - creating the forms etc will then be straightforward.
    The key is knowing what type of data should be captured on each form

    thanks
    Attached Files Attached Files
    Last edited by kev_; 01-14-2017 at 06:44 AM.

  3. #3
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,860

    Re: Creating a project tracker

    I modify in some areas as per "kev_" file.
    In this file i modify "DataValidation" & using index match formula base criteria with "Problem Number".
    See attach file. I hope this will something help for you, because i have no idea what is your data format structures.
    Attached Files Attached Files


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  4. #4
    Forum Contributor sorensjp's Avatar
    Join Date
    12-23-2015
    Location
    Yokosuka, JP
    MS-Off Ver
    2007 and 2016
    Posts
    188

    Re: Creating a project tracker

    kev_,

    I appreciate your inputs on this. I didn't expect the workbook you created. Your thoughts have given me a direction and you have certainly put a few ideas in my head. I agree the Form 1 & 2 concept is a good idea. I will try to formulate a workbook over the next few days and toss it back on here. My Dashboard thought was to simply show the most current status of each problem formatted in an easy to read printable format. Most people at work are afraid of excel so one goal is to make is Simple and Easy to navigate. Thank you for your efforts.

    Jim
    Last edited by sorensjp; 01-14-2017 at 05:45 PM.

  5. #5
    Registered User
    Join Date
    01-08-2017
    Location
    Bangladesh
    MS-Off Ver
    2012
    Posts
    7

    Re: Creating a project tracker

    That's very good idea @sorensjp. And make excel easy to use. This will help all of the excel users to use this program successfully.

  6. #6
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,239

    Re: Creating a project tracker

    Hi Jim

    I have put together a simple UserForm, roughly based on what was uploaded on post#2.
    Workbook is attached.

    I suggest you have a go at imputting some data into the UserForm- it may help your thinking.
    Perhaps you already have a manual form that captures much of this data - so easiest path could be to follow its layout.

    Form1 has been created . It a 2 page form
    Form2 will be driven by some of the fields from Form1 - so no effort made on Form2
    There are no controls built in yet - so try to enter sensible values only!

    The first few boxes are pre-filled
    - Problem number is autogenerated and
    - date and time pre-filled with current date and time
    After that it's a mixture of
    - dropdown lists,
    - spin buttons and
    - free-format text

    I have entered a few lines of dubious data, to test that it works.
    Perhaps you already have a numbering system (or more than 1 !) - this could be incorporated easily

    After you enter each new problem it takes you to the Problem Log so that you can see how it likes what you have entered

    Form1a.jpg

    Form1b.jpg
    Attached Files Attached Files

  7. #7
    Forum Contributor sorensjp's Avatar
    Join Date
    12-23-2015
    Location
    Yokosuka, JP
    MS-Off Ver
    2007 and 2016
    Posts
    188

    Re: Creating a project tracker

    kev_,

    Wow, this well thought out. I see it have great potential in application and use. I will have to tinker around with it to understand a few things and to see if/what I need to be changed.

    This is really a great starting point (much more than I thought someone would do). Allow me a few days to mess with it before I repost. Thank you so much for your assistance.

    Jim

    P.S. How is Newquay? (I lived there in 2007-09)

  8. #8
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,239

    Re: Creating a project tracker

    Take your time - and do not take too much notice of what I have put together - pretty much a shot in the dark. But hopefully, it gives you an idea of what can be achieved quite easily.

    Ultimately, the key is knowing what you want to appear on the dashboard so that we set up the fields to capture the data in the right way from the start.

    Plan it all out roughly on a bit of paper first - the dashboard >> follow_up details >> problem log . Many people jump into the worksheet without knowing where they are heading - leading to some painful re-working.

    I am happy to set up the initial starting point, but after that I would prefer to guide you in how to modify things so that it becomes your baby rather than mine! I will run through all the coding with you and explain what everything does.

    I have not been to Newquay for about 5 years - but I know it is quite a busy place and continues to draw the surfers and their entourage in spades!

    Kev

  9. #9
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,239

    Re: Creating a project tracker

    Attached is a simple planning document to help you get your thoughts together in a manner that will allow everything to be created effectively from the beginning.
    Given who you work for, there may be a few things that you do not want to declare in a public forum. In those cases, give the field name a pseudonym - what matters is what type of field it is (numeric, alpha, date etc) - so that the data is dealt with appropriately.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Creating an attendance tracker, Help
    By jsquarity in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-25-2014, 12:01 PM
  2. [SOLVED] Project supplies tracker
    By makinmomb in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 04-12-2014, 12:55 PM
  3. Need help with creating an attendance tracker.
    By ksmith8128 in forum Excel General
    Replies: 1
    Last Post: 02-26-2014, 12:49 AM
  4. Task \Project Tracker = determine output time based on variable inputs
    By Journeyman3000 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-10-2013, 04:37 PM
  5. [SOLVED] Project Timeline Tracker
    By AnnaG87 in forum Excel General
    Replies: 1
    Last Post: 04-30-2013, 11:39 PM
  6. Replies: 0
    Last Post: 06-14-2012, 02:49 PM
  7. Need help with excel Project tracker Macro
    By david.snellen in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-16-2010, 09:47 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1