asked on

Excel Status Report Workbook - Data Entry worksheet

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

mig1980

ASKER

Attached is the document I have been working with.
Blank-2012-Tracking-r1.xlsx

dlmille

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

dlmille

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

mig1980

ASKER

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.

dlmille

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

dlmille

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

mig1980

ASKER

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.

dlmille

Ok - here's the final with Task Entry using the alternative approach.

Cheers,

Dave

mig1980

ASKER

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

dlmille

I did miss that

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

dlmille

Here it is. Sorry for the wait.

Dave
Blank-2012-Tracking-r4.xlsm

mig1980

ASKER

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

dlmille

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

dlmille

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

dlmille

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

mig1980

ASKER

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.

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dlmille

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

mig1980

ASKER

I think this will work just fine. Thank you very much.