Populate Column based on date range

[Abrooksdavis]

Hi all,

I'm wondering if anyone is able to help me. This is driving me crazy!

I have multiple charts which contain environmental monitoring data: These are based upon data pulled from colums containing dates sampling took place and then adjacent columns containing microbial colony counts.

for example:

Column A is titled 'Dates' and contains a list of dates.
Column B, C, D, E etc contains numerical counts for location 1, 2, 3, 4, etc

I need to generate reports based on this data and I have to manually change the date range on dozens of charts which takes an extremely long time. Is there a way using data validation drop down menus to specify a date range for these charts?

I had the idea that I could select the date range and export this range from column A and all adjacent Count data to another worksheet and base the charts on this but I'm struggling with how to export this selection of data and display it on another worksheet. I know how to set up the drop down boxes but just cant find a way to acheive what I'm after.

Below is a crude example of the type of data I use. Any help would be greately appreciated!

Book1
	A	B	C	D	E	F	G
1	Date	Counts location 1	Counts location 2	Counts location 3	Counts location 4	Counts location 5	Counts location 6
2	15 Jan 2020	0	0	7	3	3	3
3	28 Jan 2020	0	0	36	18	24	21
4	12 Feb 2020	2	0	8	6	6	4
5	27 Feb 2020	0	0	1	0	0	0
6	11 Mar 2020	1	0	16	7	4	9
7	26 Mar 2020	0	1	6	5	3	0
8	08 Apr 2020	0	0	3	5	2	0
9	22 Apr 2020	0	0	6	4	7	5
10	06 May 2020	0	0	7	12	12	4
11	20 May 2020	0	0	10	13	11	5
12	03 Jun 2020	0	0	0	3	9	2
13	17 Jun 2020	0	0	26	14	8	10
14	01 Jul 2020	0	0	14	10	8	2
15	15 Jul 2020	0	1	4	2	4	2
16	22 Jul 2020	0	1	0	0	0	0
17	29 Jul 2020	1	0	5	12	22	7
18	12-Aug-20	0	0	2	10	16	5
19	26-Aug-20	0	0	5	2	16	3
20	09-Sep-20	2	0	13	17	39	10
21	23-Sep-20	0	0	12	18	41	6
22	07-Oct-20	0	0	1	0	10	0
23	21-Oct-20	0	1	3	3	10	2
24	04-Nov-20	0	0	17	12	3	1
25	18-Nov-20	0	0	19	17	19	5
26	04-Dec-20	0	0	0	3	3	0
27	16-Dec-20	0	0	10	12	14	6
28	30-Dec-20	0	0	3	7	5	1
29	10-Jan-21	0	0	2	4	6	2
30	24-Jan-21	0	0	2	0	5	0
31	10-Feb-21	0	1	1	2	6	2
32	24-Feb-21	0	1	4	5	7	6
33	10-Mar-21	0	2	3	3	19	4
34	24-Mar-21	0	0	3	12	12	4
Sheet3

regards,

Andrew

 

[Abrooksdavis]

Just a heads up, I managed to solve this myself using VBA.

I just looped through all chart objects and set the chart minimum and maximum on the X axis to Names linked to drop down date selection menus.

Works perfectly