Hi,
I am not sure if the subject is accurate to my needs and partly due to unfamiliarity of the function in Excel I am trying to accomplish. Sorry for that up front
.
My spreadsheet is setup with a Dashboard, Data, Calculations, and Lists worksheets. In the Dashboard worksheet I managed to create a format control using a list of years in the lists worksheet. The Calculations worksheet has a cell called Year. There are many places where I have Year in the Calculations worksheet because of adjacencies to other calculations I have using DCount, DSUM, etc. The cells below cell text Year have cell formulas of =Year. The effect and working great does the following:
1) When I select 2009, 2010, 2011, etc. in the Dashboard worksheet format control, the =Year cells in the Calculations worksheet changes to all 2009 depending on how many I need and have cells configured to use formula =Year.
2) I am able to use this list and name manager for Year to change my cells in the Calculations worksheet and thereby change the calculations based on year and other lists created for the formulas I have.
What I want to do and am having a hard time figuring out, which will lead to my ultimate DAVERAGE or SUMPRODUCT solution is:
How can I select 2009 from my format control under the Dashboard worksheet and then display in the Calculation worksheet each month and year together for the year I selected in my dashboard worksheet format control? It sounds like a calendar type formula and something else.
Therefore, when I select 2009 in the Dashboard worksheet format control that is visible for all years in a box (not a dropdown, combo, radio button, check box or slider), I want to populate 12 cells in the Month column so it looks like:
The formula for Year cells is =Year
In Calculations worksheet I want the following to occur when selecting a Year from the Dashboard worksheet format control box...
Col A Col B
Year Month
2009 Jan-09
2009 Feb-09
2009 Mar-09
.
.
.
2009 Dec-09
When I select 2010 in the format control box in the Dashboard worksheet, the Month in the Calculations worksheet changes to Jan-10, Feb-10, March-10, and etc.
The root of my crux of what I am ultimately trying to achieve is getting the average per month of data in my Data worksheet for a selected year. The Daverage formula is not averaging correctly unless I use criteria such as ="<1/31/09" and =">1/1/09" for each month. However, this does not create that dynamic and automatic update I am looking for when using the format control for Year in the Dashboard worksheet, which is imperative to keeping things streamlined and not having to reference ranges of cells from Jan-09 all the way to infinity.
Capiche?
I would love for someone to take a crack at this as it's the second to last remaining element, other than getting accurate averages per month for a selected year using DAVERAGE or SUMPRODUCT.
If you need more information let me know and I will try to respond ASAP. However, the data I have is confidential so please bear with me as I try to expunge from various worksheets and formulas.
Best and Thanks in advance,
Tom
I am not sure if the subject is accurate to my needs and partly due to unfamiliarity of the function in Excel I am trying to accomplish. Sorry for that up front
.My spreadsheet is setup with a Dashboard, Data, Calculations, and Lists worksheets. In the Dashboard worksheet I managed to create a format control using a list of years in the lists worksheet. The Calculations worksheet has a cell called Year. There are many places where I have Year in the Calculations worksheet because of adjacencies to other calculations I have using DCount, DSUM, etc. The cells below cell text Year have cell formulas of =Year. The effect and working great does the following:
1) When I select 2009, 2010, 2011, etc. in the Dashboard worksheet format control, the =Year cells in the Calculations worksheet changes to all 2009 depending on how many I need and have cells configured to use formula =Year.
2) I am able to use this list and name manager for Year to change my cells in the Calculations worksheet and thereby change the calculations based on year and other lists created for the formulas I have.
What I want to do and am having a hard time figuring out, which will lead to my ultimate DAVERAGE or SUMPRODUCT solution is:
How can I select 2009 from my format control under the Dashboard worksheet and then display in the Calculation worksheet each month and year together for the year I selected in my dashboard worksheet format control? It sounds like a calendar type formula and something else.
Therefore, when I select 2009 in the Dashboard worksheet format control that is visible for all years in a box (not a dropdown, combo, radio button, check box or slider), I want to populate 12 cells in the Month column so it looks like:
The formula for Year cells is =Year
In Calculations worksheet I want the following to occur when selecting a Year from the Dashboard worksheet format control box...
Col A Col B
Year Month
2009 Jan-09
2009 Feb-09
2009 Mar-09
.
.
.
2009 Dec-09
When I select 2010 in the format control box in the Dashboard worksheet, the Month in the Calculations worksheet changes to Jan-10, Feb-10, March-10, and etc.
The root of my crux of what I am ultimately trying to achieve is getting the average per month of data in my Data worksheet for a selected year. The Daverage formula is not averaging correctly unless I use criteria such as ="<1/31/09" and =">1/1/09" for each month. However, this does not create that dynamic and automatic update I am looking for when using the format control for Year in the Dashboard worksheet, which is imperative to keeping things streamlined and not having to reference ranges of cells from Jan-09 all the way to infinity.
Capiche?
I would love for someone to take a crack at this as it's the second to last remaining element, other than getting accurate averages per month for a selected year using DAVERAGE or SUMPRODUCT.
If you need more information let me know and I will try to respond ASAP. However, the data I have is confidential so please bear with me as I try to expunge from various worksheets and formulas.
Best and Thanks in advance,
Tom
Last edited:
