# Using a formula to select data regions for a chart

#### SoCal Mitch

##### New Member
Hi All,

On a scale of 1-10 of how good I am with Excel, I would say I am solid 3. I know the basics very well and can do simple stuff and charts.

I'm trying to find a way to have a chart automatically select a specific region of data to display based on the current date.

As an example, I have 3 parameters that are entered anywhere from 1 to 4 times per day.

IE: Col A - Has the Date, Col B - Has the time, Col C - Has datapoint 1, Col D - Has datapoint 2 & Col E - Has datapoint 3.

The chart uses the date & time columns for the horizontal axis and the columns c through e as the data in the graph. Each data can range from 40 up to 200.

I have several cells set up to show me averages based on the current date. IE: one shows me the average for each for all datapoints where the date/time are equal to or greater than 1 week ago. Another for 1 month ago.

My issue is that when I do the chart, I have to right click and re-edit the data source every time I want to see a current graph.

I'm hoping there is some way where I can create a graph that will always use data points and the horizontal axis within a specific date range just like the averages. 1 to show the previous 7 days and one the previous 30.

I've been spending the past 2 weeks trying to figure this out but I'm lost and not sure if it is even possible.

If anybody can help, I'd greatly appreciate it. I'm willing to upload a sample xlsx file if needed.

Mitch

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### cyrilbrd

##### Well-known Member
Hi Mitch,
Would that proposal work for you?
named ranges for each columns of preference using offset to limit range to cells with data.

Replies
3
Views
661
Replies
1
Views
343
Replies
0
Views
2K
Replies
1
Views
376
Replies
3
Views
732

1,190,874
Messages
5,983,345
Members
439,840
Latest member
billy1989

### 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.

### Which adblocker are you using?

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

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