Converting a COLUMN formula to a ROW formula

[Jtucker10278]

trying to make a rolling 6 week chart

so if COL A has my list of weeks and COL B has my list of total

my OFFSET named range formulas are

Total =OFFSET($B$2,COUNTA($B:$B)-($C$2+1),0,$C$2,1)
Weeknum =OFFSET($A$2,COUNTA($A:$A)-($C$2+1),0,$C$2,1)
note C2 is the location of the number of rolling weeks the chart would show

This worked great in my practice sheet, the problem is in real life

ROW 7 has my Totals and
ROW 3 has my WeekNum

so i tried

Total =OFFSET($C$7,COUNTA($7:$7)-($B$2+1),0,$B$2,1)
WeekNum =OFFSET($C$3,COUNTA($3:$3)-($B$2+1),0,$B$2,1)
note B2 is the location of the number of rolling weeks the chart would show


when i do this and i go to "Select Data" for the chart and enter

Series Values: =ChartTrial!WeekNum

nothing happens. I dont get any error message it just acts like i closed the window without entering any information


Thanks for the help

 

[JoeMo]

Re: Help converting a COLUMN formula to a ROW formula

Look at the OFFSET formula in Excel Help. If you want to offset along a row, you need to incorporate the offset in the third argument (the column offset, not the second argument which is the row offset), and set the width in the fifth argument (not the fourth argument which is the height).