Chart with Editable Date Range (user specified start date)

**fateshand** · 06-11-2009, 11:50 AM

I've got an excel sheet setup that also has a chart. The X-axis of the chart is a date range. What I'd like to do is allow a user to manually change the start date.

So if the entire range of dates were 9/1/2008 - 5/25/2009, I'd like to have an input field that a user could adjust that will change the start date to whatever is entered.

So if they type in 11/1/2008 then the chart will now only show the results from 11/1/2008 - 5/25/2009. Is this possible.

Not sure if this matters as well, but my chart is on a separate sheet from the sheet the data is listed on.

**starryknight64** · 06-11-2009, 12:20 PM

Try recording a macro to do exactly what you want, then edit the code to your fitting.

So if the macro does something that uses a constant range (i.e. "A2:Z68") then you'll need to change that to the range you want.

It may also help to have an example sheet if you'd rather have us do it.

**fateshand** · 06-11-2009, 12:52 PM

Ok,

Macro makes sense. However, from your explanation it sounds like there isn't a way to create a user interface for this? Essentially I guess I'm looking for a little GUI for the user to use. I don't want the user touching the raw data (or code). I just want them to see the results on the "results sheet" and on that sheet have a GUI that allows them to adjust the starting date.

Not possible with just excel is it?

**starryknight64** · 06-11-2009, 01:13 PM

Not entirely true. You can do various things to keep the user from seeing data you don't want to be seen (like protecting sheets for instance--which I don't know how to do).

You can also straight up hide the cells you don't want to be seen. Just select the rows or columns you want to disappear, right click, and select hide.

I hope this helps!

**fateshand** · 06-11-2009, 01:35 PM

Thanks Bud! It does help on one of the questions. However, if anyone else can address the GUI type of interface or input cell that could possibly update the starting date on the chart without the user having to get into any code or anything like that, I'd appreciate it.

**starryknight64** · 06-11-2009, 01:59 PM

Would it be alright to have the user open up a spreadsheet (perhaps--this very one we're working on) and have a form pop up after it opens?

**fateshand** · 06-11-2009, 02:50 PM

Yeah, a form would be great. I think I could really put that to use.

**ChemistB** · 06-11-2009, 02:53 PM

I would think this could be solved with Named Ranges to define your dynamic range but I'm having a mental block as to how to do it specifically. :-/

**Andy Pope** · 06-12-2009, 06:03 AM

I would go with a lookup. Your chart is based on a fixed range of cells. That range of cells is populated using INDEX function.

Check this previous thread
http://www.excelforum.com/excel-char...l-clicked.html