Modify dynamic range for chart

[Cyrius]

I want to create a scroll bar for a dynamic chart. The dynamic chart only uses the last 7 columns of the dynamic table.


When creating the ranges' X and Y names, I could not make this equation grab the last 7 columns only,


=OFFSET('Consolidated Data'!$A$5,0,0,COUNTA('Consolidated Data'!$A:$A)-1,COLUMNS('Consolidated Data'!$A$4:$N$4))


I also tried adjusting the width section using =COLUMNS(all range)- COLUMNS(smaller range), but it will always grab the first seven columns.


Then I read that placing a negative width might do the job, but that did not do it.


Thank you all for your help.

 

[Hui]

Cyrius

You will need a Count / counta in there somewhere to detail the starting point and width


like


=OFFSET($A$5,0,Count($A$4:$N$4)-7,COUNTA($A:$A)-1,7)


I removed the Consolidated Data! for clarity