• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

automating the tracking hours used dashboard

O

odearja

New Member
I am working on a tracking sheet that isn't overly complicated, but my lack of manpower forces me to try and automate it as much as possible (that and automating things is just way more fun!)

Due to corporate rules, I am forced to use a specified report and a specified tracking dashboard. I realize there may be an easier or more attractive way, but that is out of my hands. You can see where I got started playing around, but the format is basically locked in.

I have two files in the link below. One represents a timecard report (source), and the other represents the dashboard (destination). My task is to collect the hours used by employee on a given day and display the time used on the dashboard on a daily basis (This isn't too far from taking a vertical list and transposing it to a horizontal list).

Rules:
The report will not always display every day of the month...only the days applicable to the timecode used. The dashboard does display everyday and if there is not a value to display, I need the cell to stay blank, otherwise I want it to display the hours used.

With the report (source) being disposable, I intend to use the results in a copy and paste values manipulation to keep the historical data as opposed to errors popping up later after the report is deleted. By the way, although this is not the goal of the post, if anyone has any idea how to make that part easier.... I am all ears.

https://drive.google.com/folderview...E3MUZON3ZlLTVTU1Rzc2hrZC1Bb1NEckU&usp=sharing

PS: one of these days I hope to add a column at the end that utilizes a 12 month rolling sum with an output of hours used using the sum & today() functions.
 
The name will often be repeated for multiple lines on the source report, but not on the dashboard.

The report creates a separate line item for each instance.

This should be easy enough, lookup a name in a list that matches the name in Column A, and also matches this =day() in row2. If a match can't be found, return a blank cell

......but I am mentally drawing a blank. I think I am making it harder than it has to be
 
See modification made to "timecard sample". Download both and update data link as needed.

You need to first figure out how to combine date info in Workbook2 to format that can be used in formula. There are several ways of doing this, depending on your regional date format. But I find below method easiest to work with.

You can concatenate the info then convert to value. Note that you may need to adjust the order inside VALUE().
For C3 in Workbook2
Code: 
=VALUE(C$2&"-"&$B$2&"-"&YEAR(Today()))

Combine it with INDEX, MATCH for multiple criteria, remembering to enter it as array (CSE). And you are good to go.

Leaving blanks in Column A of "timecard sample" when there is data in same row in Column B, will add extra steps to calculate row# of non-blank cells. Then using OFFSET function to adjust range of array for INDEX,MATCH. You will also need to figure out the logic after the last name in Column A as all rows below will be blank and can't use it for determining array position.

See attached.
 

Attachments

You must log in or register to reply here.
Back
Top