We need to display graphs which are rolling-13 weeks. We have been able to get the week numbers to roll. The data is on the same sheet on the left hand side - columns BI to DH). The charts' source data that we are going to make flexible (13-wk rolling) is towards the right end of the sheet (cols GA to GM). How do we get the values from the left side to this chart area (GA to GM) in such a way that when the week number on the top changes, the data should change below to point to the correct week?
Rolling 13 wks data not working but rolling header working
- Thread starter arlu1201
- Start date
Arlu
If columns GA to GM are numbered 1 to 13 or as Dates 2/2/10, 9/2/10, 16/2/10 (D/m/y or any other format), you can use a Hlookup to retrieve the data for each row by referencing the date in the column above.
If your data area BI1:DHx is named Data
in any cell below GA1 type the following
=HLOOKUP(GA$1,Data,ROW())
and then copy across to GMx
Then base your chart on GA1:GMx
If GA1 is 2/2/2010 and GB1 adds 7 to GA1 etc across to GM1
By Changing the values of GA1 you can change the date range and hence the chart will move
This can be linked to a Form Control to control the starting date of the range
Another way to make dynamic charts is Named Ranges, but read about that here
http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/
If columns GA to GM are numbered 1 to 13 or as Dates 2/2/10, 9/2/10, 16/2/10 (D/m/y or any other format), you can use a Hlookup to retrieve the data for each row by referencing the date in the column above.
If your data area BI1:DHx is named Data
in any cell below GA1 type the following
=HLOOKUP(GA$1,Data,ROW())
and then copy across to GMx
Then base your chart on GA1:GMx
If GA1 is 2/2/2010 and GB1 adds 7 to GA1 etc across to GM1
By Changing the values of GA1 you can change the date range and hence the chart will move
This can be linked to a Form Control to control the starting date of the range
Another way to make dynamic charts is Named Ranges, but read about that here
http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/
Arlu
I can't replicate your problem, as Hlookup is working fine with values, equations and even mixed case Wk01 vs wk01.
I would suggest that you check that the equations in BI1:DH1 and GA1:GM1 aren't adding any leading spaces, trailing spaces or spaces in the middle.
ie:
Normal "Wk01"
Leading " Wk01"
Trailing "Wk01 "
Mid "Wk 01"
will all be treated differently by HLookup
I can't replicate your problem, as Hlookup is working fine with values, equations and even mixed case Wk01 vs wk01.
I would suggest that you check that the equations in BI1:DH1 and GA1:GM1 aren't adding any leading spaces, trailing spaces or spaces in the middle.
ie:
Normal "Wk01"
Leading " Wk01"
Trailing "Wk01 "
Mid "Wk 01"
will all be treated differently by HLookup