Dynamic x-axis chart

Hey guys, I have a graph that represents a monthly average of scores that we collect from customers and I need to be able to show that graph with dynamic starting and ending dates. I have attached a copy of the spreadsheet to look at.

The cells in blue should be the cells that the user changes to display what time span they want to look at. I created the Update Graph button but havn't built anything behind it. If you guys could give me a hand that would be much appreciated. Thanks!

Example.xlsx

G'day Neon,

My example I've applied to your example attachment is from the great Andy Pope.

Click here for Andy's instructions.

Hi NeoFlex,

See the attached workbook where I have used define names for chart data sources

Example(41).xlsx

press ctrl + F3 to see define names and their formulas. thx

Regards,
DILIPandey
<click on below * if this helps>

Hey dilipandey, that was exactly what i was looking for. However, after testing it out for a bit I noticed one error I couldnt figure out. When setting the date range, if I set the End Date then set the Start Date it will push the graph axis further into the future than what is specified. I think it has to do with the Offset in the Define Names area but I couldnt figure it out when i looked. Any help would be greatly appreciated again, thanks!

Okay.. thx for the heads up.

Defined name formula revised as :-

Formula:

Please Login or Register  to view this content.

should work now, see attached:-
Example(1).xlsx

Regards,
DILIPandey
<click on below * if this helps>

Hey dilipandey, so that didnt exactly fix the problem I was having. If you start with the dates as START: 12/31/12 & END: 4/30/13 .....then....change the start date to START: 1/31/12 it will push the end date forward one month to 5/31/12. I think it just has to do with when the OFFSET calculates what numbers its going to pull. However, if you can somehow get that fixed that would be GREAT!

BUT....

Another issue I have, and a little more important than the graph one is making the date 'define name' range dynamic. Right now it is harcoded as $K$8:$K$18 however, that grey table to the right will expand as time goes on so I need it to catch all numbers after K8...so something like $K$8:$K$COUNTA($K$8:$K$100) but maybe something a bit neater and written better.

Thanks again for all your help you've been, I really do appreciate it.

(I reposted an updated spreadsheet)

Okay.. now see the revised attachment

Example(2).xlsx


Regards,
DILIPandey
<click on below * if this helps>

That's perfect. Thanks for ALL your help dilipandey!

you are welcome NeoFlex... cheers



Regards,
DILIPandey
<click on below * if this helps>