Hi all,
I have created an excel spreadsheet to track staff performance by day. I now need help to be able to collate the info at the end of the month. I presume using v lookup. see screenshoot of daily tracker. I now need a seperate tab collating all the info.
example.JPG
You're going to need to supply a lot more info on what you're actually looking for.
Are you wanting to collate per person? are you after totals in a date range? overall? etc etc
Please upload an example of your workbook with a collated tab showing the values you expect to see.
If someone has helped you then please add to their Reputation
each sheet shown in the screen grab will track results for a month by day. I then need a master sheet that then collates all the information by name broken down into each catagory. i.e total calls, total number of absent days etc
Your screengrab doesn't give enough info on how the sheet is structured though, you definitely do not need VLOOKUP to do the collation, its either going to be use of SUMIF/COUNITF's or straightforward SUM() but like I say it all depends on how the workbook is structured.
I've attached my file. hopefully that will help? if the format needs to change that is ok.
On your totals sheet place the following in B2 and then copy across and down (so all cells (B2:I6) are included)
This will cover all of the fields that work as sum, the only one that doesn't SUM is the Absence column, you'll need to explain what you would expect as results ie if someone has 10 N's, 2 S's and 10 H's what is the expected result?
Please Login or Register to view this content.
In your example though it's not clear whether the Staff1 to Staff5 relates directly to the 5 names in the totals sheet, I've assumed they do for the purposes of this ie the first staff member on totals is the 1st on September and so on.
You have posted this in the Outlook forum, but it looks like it is an excel question. I will move it there for you
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
pjwhitfield - thats fantastic. just what I needed... it doesn't quote work for the absence and late columns. How would I collate that? currently for absence i've got 3 options, N-in work, S-Sick, H-Holiday, would there be anyway to collate this? If needed i can reduce to something more simple? Or would it be easier to use your existing formula and use "0" for being in work and "1" for off sick or late etc?
Hope that makes sense
For the two columns marked Absence H and Absence S you could use the following:Formula:
Please Login or Register to view this content.
As for the column marked Late the only thing we are given is N which is assumed to mean in or on time. If you could let us know the other possible entries then we might be able to write a formula for Late also.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks