Update Chart Based on Drop Down List

Question

Hi,

I am using a running table that will be updated daily. There are three fields of data:

A         B       C

Name Date Count

Here is an Example:

Name	Date	Count
Joe Schmo	12/3/2019	4
John Smith	12/3/2019	1
Joe Schmo	12/4/2019	5

Each day a new entry would be added, such as a new name, hence why a table is used to capture these changes.

What I want to do is create a separate sheet in the work book that allows for the following:

• Drop down list where a user can select the Name

Once a name is selected, a line chart will display and show the data for the specified name. It will have the axis Date and Count to show the daily change in count for the specified name.

So if the drop down list selects Joe Schmo, the chart would show the data from Joe Schmo in a line chart for 12/3/2019 with a count of 4 and 12/4/2019 with a count of 5. 

If in the drop down John Smith is selected, the chart would show the data from John Smith in the same kind of line chart for 12/3/2019 with a count of 1. Once a new entry is added to the table (example John Smith, date 12/5/2019, count 6), the chart would update to then show this information as well, so long as John Smith is selected.

I've looked into INDIRECT and MATCH formulas but can't seem to figure out how to do this.

Can someone lend me a hand?

Thanks!

Answer 1

Hi Adam,

Actually, I agree with Patrick, using the Pivot Table and Pivot chart is the most efficient method to achieve what you request.

However, if you still want to achieve the effect like the screenshot you attached. I have made a sample demo Excel workbook. Please check the link below:

DropDownChartTest.xlsx

This might be not the most simple way, but it does the trick. The drop down list on the cell I4. And the new user name added to the table will appear in the F column and drop down list automatically.

Hope this could help you. Let me know if you have any concern on this.

Regards,

Alex Chen

Answer 2

Hi Adam,

Have you checked the sample workbook I provide?

Please let me know if you have further concern on this question.

Regards,

Alex Chen

Answer 3

Yes I am aware of pivot tables and that is fine but I want to create a dashboard that displays a Line Chart based on a drop down list. So if I select John Smith on the drop down, the Line Chart will display that data. Essentially I want something that works like the following images:

 

Answer 4

Hi Adam

Do you know anything about pivot tables?

I think you will find a solution using Pivot Tables.