Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2015-01-28
6
Medium Priority
?
206 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 34

Assisted Solution

by:Rob Henson
Rob Henson earned 501 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 31

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 31

Accepted Solution

by:
gowflow earned 498 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 501 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

877 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