Make a Chart that will constantly change as different selections are made by a person

[jc553570]

Hello,

I am fairly inexperienced when it comes to excel and the formulas. I know the basic formulas and commands, but that is about it. I have a problem, and I am unsure if it is even solvable.

With the help of another user on this forum, I have created a spreadsheet which uses data validation to allow a user to select a company, start Date, end date, and projected months in order to output different figures. I have attached a spreadsheet to this post in case that was hard to understand.

Since there is data validation, indexing, and matching going on, as different selections are made, various tables change as a result. If there is anyone willing to help, you will be able to see what I mean as you open the example sheet and start messing around with the data validation boxes.

The problem I am encountering now, is creating a chart that will also keep changing as the user changes their selections in the data validation boxes. I am unsure if this is even possible, but I figured it was worth a shot to ask. On the example sheet, I have included a graph similar to the one I want, but it only contains data in a certain range of cells, rather than dynamically changing to accommodate whatever data is outputted.


Here is what I am looking for in the graph:
1. Whatever dates are selected by the user in the data validation boxes, along with the projected dates will be on the x-axis.
2. The PMPM figures associated with the selected dates, and the projected dates on the y-axis.
3. Ability to make different selections from the data validation boxes and still have a dynamic chart that keeps updating itself.

Like I said, I am unsure if this is even possible, but any help that you can offer will be greatly appreciated. If you still are unsure what I am trying to do, please let me know and I will try to explain it in a different way.

Thank you so much!

[cbatrody]

Hi,

This can be done using dynamic range. Can you please mock up a sample chart for one range (may be in mspaint as well if not in excel)?

[jc553570]

Hello, and thank you for responding. I have included an example spreadsheet which includes a sample chart. The series named "1" includes the ranges that I am interested in. The chart is located underneath the projections in the DROP DOWNS sheet of the workbook.

If that is not what you are looking for, maybe I am simply misunderstanding your question?

On the x-axis I want:

The output of dates, which comes from the data validation box selections, as well as the dates listed in the projected columns.

On the y-axis I want:

The output of numerical values that come from the data validation box selections, as well as the numerical PMPM values listed in the projected columns.

Please get back to me!

Thanks for your help!

Jeremy