change chart source data bssed on a cell's value

[leaning]

If cell A1 equals 3, then the graph source data range is B1:D5. If A1 equals 7, the the graph source data is B1:D10.
Can this be done?
(I have a graph of initial investment and then the yearly change based on annual fees. This project goes for 7 years, but I like to use the same graph if the project only lasts 3 years.
Thanks!
leaning

[protonLeah]

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

[leaning]

Here's the file. What I'd like is to just use one set of axis and display either the three year or the 7 year data.

[MrShorty]

I could see a filter doing this: http://www.wikihow.com/Use-AutoFilter-in-MS-Excel
Or I could see doing this with a dynamic named range: http://peltiertech.com/dynamic-charts/

Which approach do you want to pursue? Where do you get stuck?

[leaning]

Mr Shorty,

1) I want to lock the sheet.
2) User inputs a 3 or a 7.
3) User inputs initial investment
4) User inputs total annual fees (those will remain constant over the 3 or 7 year period.)

Those three inputs should be enough to draw a line.

Then the graphs get created without the user having to select cells or manually apply a filter.

Maybe even show both graphs and if the input is 3, hide the 7 graph, and reverse if the input is 7.

HTH.

leaning

[MrShorty]

Did you try using dynamic named ranges? It seems like that should work, even on a protected sheet.

[leaning]

Mr. Shorty,

I got it to work like I want.

I just changed the chart type to "Scatter with Smooth Lines".

Now, there's just the three unlocked cells the user fills out, and then the chart automatically adjusts to show 3 or 7 years.

Thanks for your help!

leaning