Changing selected data in a chart in response to a user selection

[MimiM]

Hi all

Is the following at all possible?

I have data in a crosstab format, containing dates and values in groups. My idea was to present the data for a specific week based on a user selection from a drop-down list. I attach a sample file - please look under the Adult tab. Ideally, when the user selects 15/02/2019, one line will appear, with the number of people in the relevant bin. when the selection changes to 22/02/2019, the new series should reflect the relevant line.

Please let me know if I'm making sense

Thank you in advance

PS - Apparently I cannot post an attachment. So the layout is below

Date	-6	-5	-4	-3	-2	-1	0	1	2	3	4	5	6	7	8	9	10+	Total		Adult WL
15/02/2019	9	5	4	7	4	2	1	0	4	0	1	1	2	1	0	0	0		Period	22/02/2019
22/02/2019	7	8	5	4	6	1	0	0	0	1	0	0	1	1	0	0	0
1/03/2019
8/03/2019
15/03/2019
22/03/2019
29/03/2019
5/04/2019
12/04/2019
19/04/2019
26/04/2019

<tbody>
</tbody>

a line graph covering A1:R13 is below.

Hope this helps - I have highlighted the user's current choice in red

 

[duggie33]

Hi MimiM,

I have accomplished this both using VBA and Named Ranges. There are some good examples if you google "Excel Dynamic Chart Range based on Cell Value".

For the non-VBA method, use data validation for your dropdown list of dates and the Offset and Match functions to create your named range based on selected date.

Hope that helps,

Doug

 

[MimiM]

Thank you, Doug

I have used data validation for the drop-down list but will obviously need to research the Offset function. Thanks again