• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rolling 13 wks data not working but rolling header working


New Member
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?

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


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

Our columns GA to GM are not numbered thru dates but as Wk01, wk02, etc. Also we observed that if the wk number field is a value, hlookup works. If its a formula, it doesnt. Does hlookup work only if the value you are looking up is a value and not a formula?

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.


Normal "Wk01"

Leading " Wk01"

Trailing "Wk01 "

Mid "Wk 01"

will all be treated differently by HLookup