Solved

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

Posted on 2015-01-28
6
154 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 31

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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 the scrolling table in Microsoft Excel using the INDEX function.

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now