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

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
Beanburrito04Asked:
Who is Participating?
 
gowflowConnect With a Mentor Commented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Yes, it's possible, but all you have given is the dashboard, not the background information.
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
gowflowCommented:
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
 
Katie PierceConnect With a Mentor Commented:
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
 
Beanburrito04Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.