Dynamic Dates on Chart

Barren_Wuffett

New Member
Joined
Aug 6, 2019
Messages
18
Hey everyone, I have a fixed table with dates (x-axis on desired chart) that go for several years into the future, but data (y-axis) that only goes a few months. This will be a working file where new data is added each month. I'd like an area chart to show the data across the dates that have already happened (only the dates that have data). But if I select all of the data for the chart, the x-axis goes all the way out to the last date, which has no data. I'd like the chart to only show the dates for which I have data (x-axis automatically gets longer when new data is inputted each month). I know I could just adjust the chart data area every time, but I'd prefer it to be dynamic. Any ideas?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
IF Your Y-Axis Data s in Column A from A2 to A100 AND Your X-Axis Data s in Column B from B2 to B100
Go to Formula tab and then Name Manager, New And Then add one Names without Space e.g. ChartValues
And at Referto Section Write (change sheet2 to your sheet name):
Excel Formula:
=OFFSET(Sheet2!$B$2,,,COUNTIF(Sheet2!$B$2:$B$100,"<>" & ""))
AND again for Y-Axis go to New And Then add one Names without Space e.g. ChartMonths
And at Referto Section Write (change sheet2 to your sheet name):
Excel Formula:
=OFFSET(Sheet2!$A$2,,,COUNTIF(Sheet2!$B$2:$B$100,"<>" & ""))

Now go to Insert Tab and Select Chart Type, Go to Chart Design Tab and Select Data
Remove Previous data for that part and again add New Series, at Series Value Write:
Excel Formula:
=Sheet2!ChartValues
And At Horizontal axis Label Hit Edit and write:
Excel Formula:
=Sheet2!ChartMonths

Now your dynamic chart ready.
 
Upvote 0
Thanks, Maabadi. I see what you're doing. Can you help me change your instruction to match what's actually in my workbook? I have several categories for the Y-Axis, and my content is in rows, not columns. Dates are in C16:AF16 as of now. And my data is in C17:AF21. Category labels are in B17:B21.

Thanks!
 
Upvote 0
1. Please Update your Acount Details to we know you use which OS & Excel Version.
2. Please Upload example File with 5-15 rows with XL2BB ADDIN (Preferable) or At free hosting site e.g Google drive, Onedrive or www.Dropbox.com and Insert Link Here.
 
Upvote 0
AND I think you have multiple Category for X-AXIS Not Y-AXIS
For ChartMonths Use this formula
Excel Formula:
=OFFSET(Sheet2!$C$3,,,,COUNTIF(Sheet2!$C$4:$AF$4,"<>" & ""))
AND for Values (Categories from 1 to 10) add Names (I add Here Category1 to 10). For Category1:
Excel Formula:
=OFFSET(Sheet2!$C$4,,,,COUNTIF(Sheet2!$C$4:$AF$4,"<>" & ""))

AND for other Category Add row number 1 by 1.
Category 2 is $C$5 & Sheet2!$C$5:$AF$5
AND So on.
 
Upvote 0
Try This:

Book2

If you want change X & Y column go to select Data and Switch Row/Column
 
Upvote 0
Solution

Forum statistics

Threads
1,223,886
Messages
6,175,190
Members
452,616
Latest member
intern444

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
Back
Top