Solved

How can I use Excel to track weekly, monthly, quarterly and annual numbers on one page?

Posted on 2015-01-28
6
162 Views
Last Modified: 2016-02-11
I'm not sure if it's possible, but I'd like to have the highlighted fields as fields that are updated on a weekly basis with new numbers.  I'd like the columns beside it to run the cumulative numbers on a weekly, monthly, quarterly and annual basis, but not sure if that is possible without having to have a separate sheet with a separate line for each week.

I've never created a dashboard like this before, but wondering if there is a way to make it work?  

I'll attach a sample file of what I'm looking to create.  Open to suggestions on formatting and general layout as long as the end result is close to what I've described above.

Help!
Scoreboard-TEST.xlsx
0
Comment
Question by:Beanburrito04
6 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40575526
Yes, it's possible, but all you have given is the dashboard, not the background information.
0
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 167 total points
ID: 40575621
As Phillip says, more details required.

AS an aside, I can't help noticing the syntax of some of the formulas used on the sheet. For example, the % of Target reached formula in cell D5:

=SUM(B5/C5)

You don't need to include this in a SUM() function, you can use just:

=B5/C5

In the case where C5 becomes zero, dividing by zero gives the #Div0 error message; you can avoid this using:

=IFERROR(B5/C5,0)

Thanks
Rob H
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40575755
Some questions:
but I'd like to have the highlighted fields as fields that are updated on a weekly basis with new numbers.

How is the data updated to these fields manually ? thru input or it is pulled from other sources ?

I'd like the columns beside it to run the cumulative numbers on a weekly, monthly, quarterly and annual basis, but not sure if that is possible without having to have a separate sheet with a separate line for each week.

Yes it is possible but all depends on how initially the data is updated. The rest can be via VBA or simply formulas. But you need to advise on how you intend to put the data each week etc...

To help in guiding you this is how it is usually done.
You will have a sheet called Data in which the data is laid out in fields/columns and is updated regularly and then the other sheet is called Dashboard or front end and all the mechanism of what you need is either formula driven or VBA depends on how complex the result is requested to be,

Do you have a sample data that covers the dashboard that you designed ? if yes then post it and will advise some results and you can then take it from there.

gowlfow
0
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 29

Accepted Solution

by:
gowflow earned 166 total points
ID: 40575843
Just as an example to my previous comment I created a small macro that you can activate with the button Update week it will only update the first block that is yellow by adding the week to Cumul and then clearing the week for a new update.

We can extend this to all the rest.

Load the file activate macros and try it. Once you update then add new data and press on update and see how it works.
gowflow
Scoreboard-TEST.xlsm
0
 
LVL 7

Assisted Solution

by:Katie Pierce
Katie Pierce earned 167 total points
ID: 40576158
As gowflow asked, how is the data being entered?  Manual input?

Because months, quarters and years do not line up nicely against weeks.  A month may incorporate half of one week, three full weeks following, then another half a week or so.

Do you have some raw source data, maybe in daily format, that you can include as its own tab?  Then you can make formulas that look to that data.
0
 

Author Comment

by:Beanburrito04
ID: 40614958
I'm sorry for the delay in response!  I have reconfigured the spreadsheet to include some of the feedback above.  Now I have the following questions:

1.  How can the most recent entry only (for each recruiter/client services person) to reflect on the 2015 scoreboard tab?  Is that possible?

2.  If we have to add a new sourcer/recruiter/client services tab, how can I ensure the appropriate format, fields, and formulas are created without having to manually redo all the formulas again?

3.  Lock the 1st and last tabs (only one person can access the data)?

Please see attached updated file!

Leanne
Scoreboard-2015-V2.xlsx
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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.

810 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