Solved

Excel Status Report Workbook  Automation

Posted on 2012-03-28
18
396 Views
1 Endorsement
Last Modified: 2012-04-02
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.
My question is, I trying to make data entry simpler but am very new to Excel. How can I enter the data in one place and have it replicated into the other areas I need it to? Some detailed help would be grateful.

Thank you
1
Comment
Question by:mig1980
  • 10
  • 8
18 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37779663
If you can mock up an example, or post your current workbook after obfuscating (to ensure non-sensitive) the data, it would be easier to help/explain, etc.

Basically, you reference your data entry sheet from other sheets in the workbook.

Attached, find an extremely simple example of that, with a couple direct links and a vlookup example against a table of tasks - but easier to explain to you if you post something.

Dave
dataEntryToReport-r1.xls
0
 

Author Comment

by:mig1980
ID: 37782741
Good day dlmille. Attached is the reporting spreadsheet that I use. Hopefully you can help me make it more robust and easier to use.

I was also thinking something along the lines of having a data input worksheet that I could fill out the proper areas and when I clicked an "add" button it would populate the All tasks worksheet and the weekly worksheet.

Thank you
Blank-2012-Tracking.xlsx
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37782789
Why don't you mock up a starting example of your user input sheet as that's the main focus of the question - to connect that to the report right?

If you havent already...  I'm away from my office for a few but will assist by afternoon.
0
 

Author Comment

by:mig1980
ID: 37783209
Thank you for the fast response. I am attaching the workbook with another worksheet marked Task Entry. That is a mockup of what I envision.

When the Add Task button is clicked, the fields are copied to the Jan-Tasks list and depending on the "Week Ending Date" to the corresponding weekly report under the corresponding task number section.

Thank you
Blank-2012-Tracking.xlsx
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37784854
I've tried to use your add-task button and get an error: "Formsheet.xls not found".  I either need a functioning button so I can see where all the info goes, or you need to generate sufficient data so I can then show you how to create linkages to your output tabs, per original request.

Cheers,

Dave
0
 

Author Comment

by:mig1980
ID: 37785004
I simply created a mockup of the data entry sheet. I didn't realize you wanted it functioning. I am unsure how to get it to function correctly to move the data to the proper sheets and locations.

What data would you need from me to show me how to create the linkages to the output tabs?

Thank you
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37785126
Create a dozen or so tasks, or just take a completed task data entry output sheet that you have and if the data is not sensitive (or fudge it) then post the sheet with that data.  I have to connect the dots from your starting point, right?

Dave
0
 

Author Comment

by:mig1980
ID: 37789724
OK, here is a revised version of the previous document I attached with some manual entry as to how the data should show up in the respective task list and weekly tasks sheets. Hopefully this is enough to see what I am getting at. If not, let me know and I can see what else I can do.

Thank you
Blank-2012-Tracking.xlsx
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37789805
So, when you do data entry it goes into the Jan-TASKS worktab?  That data should be linked to the other tabs, correct?  What happens in Feb?  Why don't you just have a TASKS worktab (instead of for a given month?  Or, is the entire workbook dedicated to just one month?

Thanks,

Dave
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37791478
Ok.  I think I got it.

You enter your tasks for a given month, as this is a monthly update workbook, then you want those tasks, by task type/date based on end of week to be entered into the report sheets.  At the end, you want the total hours reported to be summarized.

I did this with the simplest formulas using a helper column (though more sophisticated formulas could be written without one, it makes it hard to debug especially if you're new at this!).

First, the helper column - in column G of your JAN-Tasks tab:

[G4]=COUNTIFS($A$4:$A4,$A4,$B$4:$B4,$B4)&$A4&$B4  and copy down to the bottom of your dataset

This lets us know what instance for the DATE and TASK NUMBER combination.  Using this, we can then look that up in the summary report sheets.

Second, on the summary report sheets, we need a cell that holds the last day of the week for matching against the data entry JAN-Tasks tab (for simplicity at this point, though the formula could be incorporated into the INDEX/MATCH formulas):

[J18]=DATEVALUE(RIGHT(C17,LEN(C17)-FIND("- ",C17)-1) & ", 2012")

This cell can be hidden by changing the font to WHITE color.

Third, we have the lookup INDEX/MATCH formulas in the ACCOMPLISHMENTS and DATE COMPLETED cells.  

[C21]=IFERROR(INDEX('Jan-TASKS'!$A$4:$F$66,MATCH((ROW()-ROW($A$21)+1)&$J$18&$B$20,'Jan-TASKS'!$G$4:$G$66,0),4),"")  and copy down for the 4 rows available.  After that, we do an edit/find replace B$20 to B20, and $A$21 to A21 so we can then copy these formulas down to the other ACCOMPLISHMENT sections.

[I21]=IFERROR(INDEX('Jan-TASKS'!$A$4:$F$66,MATCH((ROW()-ROW($A$21)+1)&$J$18&$B$20,'Jan-TASKS'!$G$4:$G$66,0),5),"") same thing here

These functions combine the INSTANCE of the Date/TaskNumber (e.g., 1DATE1-SiteVisits is the first item that is matched against the JAN-Tasks tabs), then a match is performed to find the ROW of the match, and INDEX is used against that row and column 4 for ACCOMPLISHMENT, column 5 for DATE.

Finally, we need to sum the hours worked:

[I65]=SUMIF('Jan-TASKS'!$A$4:$A$66,$J$18,'Jan-TASKS'!$F$4:$F$66)

Basically, for the week being summarized, column F Hours Worked is tallied.

Once that was completed, I regenerated the successive week's 2-5, then changed the week report using the drop down and the formulas all looked up properly.

See attached.

Cheers,

Dave
Blank-2012-Tracking-r1.xlsx
0
 

Author Comment

by:mig1980
ID: 37796711
To answer your first question, the entire workbook is dedicated to a given month. Date changes would be made and there would be an individual workbook per month.

With regards to the proposed solution, I will be testing today and will post later today if I have any further questions. I truly appreciate your time and effort.

Thank you.
0
 

Author Comment

by:mig1980
ID: 37796974
Well, it seems to work well with the exception of the Task Entry worksheet. If I fill out the Task Entry sheet and click on the "Add Task" button, it prompts me looking for another worksheet or something along those lines.

I was under the impression that would be working. Any ideas there?

Thank you
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37797488
I did nothing with the Task Entry sheet, as that was not a scoped question I understood needed to be worked on.  I focused on distributing the data from one location which appeared to be the original problem, and your Task Entry sheet appeared to be something that had already been created.

You did not upload a .XLSM so I could not test whatever you had done with the Task Entry sheet.  It appeared that you had originally created macros and hooked them up to the Add Task button.  Did you not?

Dave
0
 

Author Comment

by:mig1980
ID: 37797570
The button for the "Add Task" was created but without any coding for the macro. After getting confused on how to set that up I deleted the macro but I guess it never broke the connection.

I would prefer to have the data entry sheet to keep a consistent data entry protocol in place for the future. Is this something you could assist or should I create another question for this?

Thank you
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37797617
I think this question has been all about taking what's on the TASKS tab and putting lookups in place throughout the workbook.  If you don't mind, we can deal with the scope of the task entry sheet in another question.  I'm happy to work that as well.

Dave
0
 

Author Comment

by:mig1980
ID: 37797686
Perfect. I will go ahead and mark this question complete and open up a new one later today.

Thank you for all your help thus far.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37797691
I'm working on it already if you want to go ahead and post.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37798324
Please post here to advise when your new question is ready, and you can post the link as well.

Cheers,

Dave
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

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

13 Experts available now in Live!

Get 1:1 Help Now