Off the bat, I'm sorry if this is a dumb question....I am not an excel expert.
I am working on a sales and call volume scorecard for work (worksheet A). I will just use one set of numbers (call volume) as an example, but in the end, there will be many, many pieces of data I have to use.
On worksheet A, I need to have the previous days' # of calls, the week to date # of calls, quarter to date, and year to date. These all need to be compared to previous WTD, previous QTD and previous YTD #s.
I am sure that I need a base of data in worksheet B and change the reference point on each days' scorecard.
How do get excel to recognize which week to point to without manually changing all of the formulas, or manually typing data each day? In other words, it needs to be able to "know" that if I say today is 12/9/04, then:
Week to date is 12/5/04-12/9/04
Last week to date is 11/28/04-12/2/04, etc.
What is the best way to organize the base data (worksheet B)?
Is there a reference cell I can use to point to, put in the base date that I want, and excel recognizes the parameters for all of the other formulas I need?
How do you get a cell on worksheet A that represents a value on worksheet B to update itself each day based on the new data being entered each day on worksheet B?
Am I over-thinking this?
Bottom line: how do I do this? Any help is greatly appreciated.
I am working on a sales and call volume scorecard for work (worksheet A). I will just use one set of numbers (call volume) as an example, but in the end, there will be many, many pieces of data I have to use.
On worksheet A, I need to have the previous days' # of calls, the week to date # of calls, quarter to date, and year to date. These all need to be compared to previous WTD, previous QTD and previous YTD #s.
I am sure that I need a base of data in worksheet B and change the reference point on each days' scorecard.
How do get excel to recognize which week to point to without manually changing all of the formulas, or manually typing data each day? In other words, it needs to be able to "know" that if I say today is 12/9/04, then:
Week to date is 12/5/04-12/9/04
Last week to date is 11/28/04-12/2/04, etc.
What is the best way to organize the base data (worksheet B)?
Is there a reference cell I can use to point to, put in the base date that I want, and excel recognizes the parameters for all of the other formulas I need?
How do you get a cell on worksheet A that represents a value on worksheet B to update itself each day based on the new data being entered each day on worksheet B?
Am I over-thinking this?
Bottom line: how do I do this? Any help is greatly appreciated.
