Populate Column based on date range

Abrooksdavis

New Member
Joined
Apr 9, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFG
1DateCounts location 1Counts location 2Counts location 3Counts location 4Counts location 5Counts location 6
215 Jan 2020007333
328 Jan 20200036182421
412 Feb 2020208664
527 Feb 2020001000
611 Mar 20201016749
726 Mar 2020016530
808 Apr 2020003520
922 Apr 2020006475
1006 May 202000712124
1120 May 2020001013115
1203 Jun 2020000392
1317 Jun 2020002614810
1401 Jul 202000141082
1515 Jul 2020014242
1622 Jul 2020010000
1729 Jul 202010512227
1812-Aug-2000210165
1926-Aug-200052163
2009-Sep-202013173910
2123-Sep-20001218416
2207-Oct-200010100
2321-Oct-200133102
2404-Nov-2000171231
2518-Nov-20001917195
2604-Dec-20000330
2716-Dec-20001012146
2830-Dec-20003751
2910-Jan-21002462
3024-Jan-21002050
3110-Feb-21011262
3224-Feb-21014576
3310-Mar-210233194
3424-Mar-2100312124
Sheet3


regards,

Andrew
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Abrooksdavis

New Member
Joined
Apr 9, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,594
Messages
5,637,299
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top