Dynamic Dates on Chart

[Barren_Wuffett]

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?

 

[maabadi]

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):

=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):

=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:

=Sheet2!ChartValues

And At Horizontal axis Label Hit Edit and write:

=Sheet2!ChartMonths

Now your dynamic chart ready.

 

[Barren_Wuffett]

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!

 

[maabadi]

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.

 

[Barren_Wuffett]

1. Windows and Office 365
2. Dropbox

 

[maabadi]

AND I think you have multiple Category for X-AXIS Not Y-AXIS
For ChartMonths Use this 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:

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

 

[Barren_Wuffett]

maabadi, did you notice I posted the dropbox link?

Thanks

 

[maabadi]

Try This:

Book2

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