Solved

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

Posted on 2015-01-28
6
185 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 33

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 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 31

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

617 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