splitting scorecard into 3 sections

[dougers1]

hey can some one help me- i have been giving the task of splitting my department into 3 sections
section 1 phone team
section 2 allocating team
section 3 email team

section 1 formula will be amount of calls * avg talk time
section 2 will be easier with just amount of allocation filled v requests %
section 3 will be for each email completed 4 minutes added

all of theses will be measured against the log in hours for that day.


is it possible to have a scorecard that allows the formula to change depending on what section is chosen

[newdoverman]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

[dougers1]

hi i have added the scorecard that we use just now for another department , i was thinking that we could use this for data input (could remove some of the columns as they would not be needed. i have shown in if possible sheet a small idea with what i would like to achieve


Thanks

[newdoverman]

I suggest that you use the first scorecard and adapt it to your situation, removing the columns that you don't need and adding the ones that you do need. Instead of having a totals row at the bottom of the table, use a Pivot Table to summarize the data (shown on Sheet1)

Notice on the Data Input worksheet, I have used IFERROR to eliminate the error values which cleans up the data. To add new data, just add to the bottom of the table. The table will expand to accommodate the new data. With new data added, refresh the Pivot Table and the summary is brought up to date.

You can create multiple pivot tables to separate the types of statistics you want to gather.

I don't quite understand the worksheet that shows what you want. From what I do understand, it is able to be done with multiple formulae to filter out the data required from the Data Input worksheet.

[dougers1]

That would work but as the months go on the pivot table will be massive - i know i can group by months but is there a way that i can ungroup a single month in the pivot table and select specific date

[newdoverman]

If you group by Months and Days, you can select the month and an individual date. You can change the grouping without destroying the Pivot Table in order to suit what you want to show.

I have been keeping heating and AC statistics for my home for 10 years and from the pivot table can pick out a single date from those 10 years...no problem.

When you group by days, all days in every month become available to you. By using the filters, choose the Month, and the day of the month and you will be presented with the values for that date or group of dates.

[dougers1]

great can see that now - i have tried to create another table from the data and when i select Date Dept Name - in rows they all come under row A - how did you manage to get them to go across without adding them as columns in the pivot ?

[newdoverman]

Click anywhere in the pivot table. Click on the Design Tab, then Report Layout (on the right side of the ribbon), choose Show in Tabular Form.