Solved

Excel Status Report Workbook - Data Entry worksheet

Posted on 2012-04-02
19
306 Views
Last Modified: 2012-04-17
Good day. I have a workbook that I use to give status reports to my project manager once a month. The workbook is as follows:

One worksheet has all tasks for the month and is where I do all of my initial entry. It has 6 columns with the following data:
1) Week Ending Date (drop down pulling form another worksheet with the ending dates of the projects e.g. January 2 - January 6)
2) Task Number (another drop down list pulling from another worksheet with different project categories e.g. 2.1-Newsletter)
3) State (if Applicable) (which I would enter the State in the USA that the task pertains to, can be empty)
4) Description (A description of the task, free text)
5) Date Completed (date the task was completed, entered by me)
6) Hrs to Complete (Hours taken to complete task)
The worksheet also has a running total of hours at the very bottom of the list.

Next there are 4 or 5 other worksheets, depending on the month and each of these worksheets is a breakdown per week of work
These worksheets are broken down by Time Period (which is the same as the Week Ending Date drop down above).
Also broken down by tasks (task numbers 1, 2.1, 2.2, 2.3, 2.4, 3, 4, 5) which are the same as the Task Number above.

Finally a total hours worked this week count at the very bottom of each worksheet.

I would like to enter the data in the mockup worksheet marked "Task Entry" and once the "Add Task" button is clicked, it would be placed in the proper areas throughout the worksheet.

I am also looking to have changes such as dates, task numbers, etc easily changeable.

Thank you
0
Comment
Question by:mig1980
  • 12
  • 7
19 Comments
 

Author Comment

by:mig1980
ID: 37798817
Attached is the document I have been working with.
Blank-2012-Tracking-r1.xlsx
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37798859
Try this and advise.  There are two approaches for the data entry.  The first, which you created, puts controls on the sheet.  I gave you code to support that.  However, there's an alternative sheet that requires no controls (Except the Add button) which is easier to support/maintain, and doesn't use ActiveX controls on a sheet, as those can cause problems.  The theoretical "proper" use for ActiveX controls is on a UserForm (which is more work) though I've done the same as you have, I just wanted to point all this out to you so you can make choices.

The code populates your TASKS tab for a given month, then Excel formulas populate the rest of your workbook based on lookup references.

Let me know which you ended up preferring.  Both have macros to support updating the tasks sheet, as follows (note V2 is on the alt data entry sheet):

Option Explicit

'Drop Down 2 - Week ending date
'Drop Down 3 - Task Number
'TextBox1 - Descriptin
'TextBox3 - State
'TextBox2 - Date Completed
'TextBox4 - Hours

Sub logTask()
Dim wkb As Workbook
Dim wks As Worksheet
Dim wksTasks As Worksheet
Dim r As Range
Dim rng As Range
Dim myControl As Shape
Dim strWeek As String
Dim strTask As String
Dim strState As String
Dim strDesc As String
Dim strCompl As String
Dim strHrs As String
Dim vOut As Variant

    Set wkb = ThisWorkbook
    Set wks = wkb.Worksheets("Task Entry")
    Set wksTasks = wkb.Worksheets("Jan-TASKS")
    
    Set rng = wksTasks.Range("A" & wksTasks.Rows.Count).End(xlUp).Offset(1, 0)
    
    
    strWeek = wks.Range("AB5").Value
    strTask = wks.Range("AA7").Value
    strState = wks.OLEObjects("TextBox3").Object.Value
    strDesc = wks.OLEObjects("TextBox1").Object.Value
    strCompl = wks.OLEObjects("TextBox2").Object.Value
    strHrs = wks.OLEObjects("TextBox4").Object.Value
    
    vOut = Split(Format(strWeek, "mmmm dd, yyyy") & "||" & strTask & "||" & strState & "||" & strDesc & "||" & strCompl, "||")
    rng.Resize(, UBound(vOut) + 1).Value = vOut
    rng.Offset(, UBound(vOut) + 1).Value = CInt(strHrs)
    
    MsgBox "Record Updated in TASKS Sheet!"
    
End Sub
Sub logTaskV2()
Dim wkb As Workbook
Dim wks As Worksheet
Dim wksTasks As Worksheet
Dim r As Range
Dim rng As Range
Dim myControl As Shape
Dim strWeek As String
Dim strTask As String
Dim strState As String
Dim strDesc As String
Dim strCompl As String
Dim strHrs As String
Dim vOut As Variant

    Set wkb = ThisWorkbook
    Set wks = wkb.Worksheets("Task Entry - Alt")
    Set wksTasks = wkb.Worksheets("Jan-TASKS")
    
    Set rng = wksTasks.Range("A" & wksTasks.Rows.Count).End(xlUp).Offset(1, 0)
    
    
    strWeek = wks.Range("Z5").Value
    strTask = wks.Range("D7").Value
    strState = wks.Range("D12").Value
    strDesc = wks.Range("D9").Value
    strCompl = wks.Range("D14").Value
    strHrs = wks.Range("D16").Value
    
    vOut = Split(Format(strWeek, "mmmm dd, yyyy") & "||" & strTask & "||" & strState & "||" & strDesc & "||" & strCompl, "||")
    rng.Resize(, UBound(vOut) + 1).Value = vOut
    rng.Offset(, UBound(vOut) + 1).Value = CInt(strHrs)
    
    MsgBox "Record Updated in TASKS Sheet!"
    
End Sub

Open in new window


Cheers,

Dave
Blank-2012-Tracking-r1.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37798980
I've added the Microsoft Date & Time Picker control to both tab options, as well as the select from state option on both tabs.

Please let me know which you prefer (I lean toward the Alt version) and if you have any outstanding issues.

See attached.

Dave
Blank-2012-Tracking-r2.xlsm
0
 

Author Comment

by:mig1980
ID: 37802254
I took a look at your last revision. It seems to populate the Tasks list correctly from the Task Entry sheet but the information does not go to the proper week report.

I would also like to have the task entry data fields to be blanked out (Hours completed, Description) and the date completed to revert back to today's date.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37802575
You are correct, and the information to the proper sheet will be corrected, post haste (its because the info was pasted as text rather than date format).

However, please advise which sheet you prefer so I don't have to duplicate and test two pieces of code:

1.  The original data entry sheet with all the controls
2.  The alt-data entry sheet.

I await your response.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37802716
Ok - with both still in effect.  I made the changes you requested.  Its posting in the detail areas, correctly now that the format of date is corrected on the paste.

If you have anything else, please ask, and also advise Task Entry or Task Entry - Alt as the primary sheet and I'll respond accordingly.  

Cheers,

Dave
Blank-2012-Tracking-r3.xlsm
0
 

Author Comment

by:mig1980
ID: 37803243
This looks great. I haven't fully tested it but it seems to be working great. I would prefer to use the Task Entry - Alt sends it keeps things simpler.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37803331
Ok - here's the final with Task Entry using the alternative approach.

Cheers,

Dave
0
 

Author Comment

by:mig1980
ID: 37812559
Good morning Dave. I don't see the final workbook attached in your response below. Let me know if you just forgot to attach it.

Thank you
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 41

Expert Comment

by:dlmille
ID: 37812719
I did miss that

Will post in a few when I get back to the office
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37814077
Here it is.  Sorry for the wait.

Dave
Blank-2012-Tracking-r4.xlsm
0
 

Author Comment

by:mig1980
ID: 37834278
Thank you for the assistance and sorry it took me so long to respond but one last question. How or what would I need to update to create a workbook per month?

Thank you
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37834316
I would suggest creating a drop down for the month you're doing.  Then, you could update your data validation drop downs to use that so the drop down for week ending date will work correctly.

I'd also suggest changing the names of the tabs to NOT have the month as part of the workbook.  Then, modify the macro code to copy to TASKS instead of Jan-TASKS.

A new question to get assistance for this would be appropriate, if needed.

Cheers,

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37841146
I spent some time thinking about this and think I have it licked - the solution got a bit complicated as I felt there was a need to update all the drop downs dynamically based on year and month being worked on, so the solution should really work for any given year, correct?  My current progress is on a computer I can't access until tomorrow.

Please let me know if you want to create a new question.  Otherwise, I'll just post it.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37843839
Ok - it was a bit of work - at least as much as already done, and mostly formulaic.  Let me know if you need any explanations - I touched a lot of stuff that was already developed in your workbook, re: drop down formulas, etc., so all that's automatic.  There are no month names in the tab, the drop downs on the first sheet control the formulas in the back.  On the first sheet, I documented what range names I created.

If you see no bugs, then I think we've maxed out the scope of this question.  

Cheers,

Dave
Blank-2012-Tracking-r5.xlsm
0
 

Author Comment

by:mig1980
ID: 37844125
This looks great and if perfect,. I only two issues with it. Number one, it seems to be calling a link to another workbook (that I must have accidentally linked) but not sure how to delete that link so that it doesn't prompt me every time I open the file. Also, there seems to be a ton of drop downs and "buttons" throughout the Task Entry tab that are not needed and expand the size of the tab to the right and to the bottom tremendously. How can I delete all of this to clean it up without doing it manually?

I also wanted to ask you what i would need to change to have this work for multiple months and years aside from the month and year drop down in the Task Entry sheet?

I also noticed that when you choose different years in the Task Entry sheet, the date in cell J18 does not change the year. This was probably an oversight since it looks like 2012 is hardcoded.

Thank you for all your help thus far and I promise this will be it.
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37844220
I fixed the link problem - not sure how it occurred, but I had to save the workbook as the name of the linked sheet, then saved back to a new name and it cleared the link.  I also got rid of all the #REF in the range name manager.

>>Also, there seems to be a ton of drop downs and "buttons" throughout the Task Entry tab that are not needed and expand the size of the tab to the right and to the bottom tremendously. How can I delete all of this to clean it up without doing it manually?

I'm not sure I understand.  You created all these drop downs and buttons on the Task Entry tab, though I did add the drop down for Date Completed and for Month and Year.  Am I seeing what you are?  

I see Month, Year, Week Ending Date, Task Number, Description, State, Date, Hours and the add task button.  That's it.

>>I also wanted to ask you what i would need to change to have this work for multiple months and years aside from the month and year drop down in the Task Entry sheet?

Please ask a new question.

>>I also noticed that when you choose different years in the Task Entry sheet, the date in cell J18 does not change the year. This was probably an oversight since it looks like 2012 is hardcoded.

As you designed, date completed initializes in the present.  

SEE your post:  >>I would also like to have the task entry data fields to be blanked out (Hours completed, Description) and the date completed to revert back to today's date.


Since you're really not going to complete work ahead of time, is this really an enhancement you need?

Let me know if you really need this last bit - that's additional scope where a worksheet event would have to be written to handle.  Plus additional logic to develop a date in the week that exists for that year and week ending combination - it in of itself worthy of a new question.

I really think asking a new question on these two items is most appropriate.  Remember, E-E experts do this VOLUNTARILY. Your original question was already extensive, and another expert MAY have asked you to break it up at the start.  Asking a follow-on question is common practice, and about two minutes time for significantly more time from experts who respond.  Think about it.

Peace!  Take care.

Dave
Blank-2012-Tracking-r6.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37845830
>> I also wanted to ask you what i would need to change to have this work for multiple months and years aside from the month and year drop down in the Task Entry sheet?

I modified all relevant formulas, so there is nothing you need to do, I believe.  See the formulas in the drop downs tab where your dates and weeks lists are.  I've put formulas for all that to be automatic.

Let me know if you have follow-up questions on these formulas.  You may have noticed the weekly dropdowns on the Task Entry as well as the wkly report tabs all automatically update as well.

Cheers,

Dave
0
 

Author Comment

by:mig1980
ID: 37857639
I think this will work just fine. Thank you very much.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now