auto adjusting axis based on cell ranges

[jaolvera]

Hello!

I have this cluster stacked bar graph that shows infectious period over a course of time, this graph will auto populate as the data is entered over time,  the patient names axis pulls from a column in another workbook, but it looks like it includes blank cells, is there a way to avoid that so the height of the graph is relevant to the actual data?

[#2]

@jaolvera 

Let's say the source data are on a sheet named Data Sheet.

x-values in A2 and down.

y-values of the first series in B2 and down.

y-values of the second series in C2 and down.

y-values of the third series in D2 and down.

Create the following named ranges - click Define Name on the Formulas tab of the ribbon:

XValues refers to =OFFSET('Data Sheet'!$A$2,0,0,COUNTA('Data Sheet'!$A$2:$A$10000),1)

Series1 refers to =OFFSET('Data Sheet'!$B$2,0,0,COUNTA('Data Sheet'!$A$2:$A$10000),1)

Series2 refers to =OFFSET('Data Sheet'!$C$2,0,0,COUNTA('Data Sheet'!$A$2:$A$10000),1)

Series3 refers to =OFFSET('Data Sheet'!$D$2,0,0,COUNTA('Data Sheet'!$A$2:$A$10000),1)

Click anywhere on the chart.

Click Select Data on the Chart Design tab of the ribbon.

Click Edit under 'Horizontal (Category) Axis Labels'.

Clear the current range, then insert

='Data Sheet'!XValues

Click OK.

Select the first series under 'Legend Entries (Series)', then click Edit.

Clear the current range, then insert

='Data Sheet'!Series1

Click OK.

Repeat these steps for the other series, with ='Data Sheet'!Series2, etc.

Finally click OK.

The chart should now be dynamic.

[#3]

I uploaded the excel file because I got stuck at the defining portion, the two worksheets are titled "person diagram" and "person graph"

@HansVogelaar 

[#4]

@jaolvera 

I don't see an attachment or a link.

[#5]

@HansVogelaar 

[#6]

@jaolvera 

Thanks. It was a bit more complicated than I thought, but here is the modified version.

You can see the defined names in Formulas > Name Manager.

[#7]

thank you! that worked!

[#8]

do you know if theres a way to auto set the minimum and maximum bounds based off the dates, currently I have to manually adjust them so that all periods are shown, when entering new data the axis bounds have to be reset each time@HansVogelaar 

[#9]

@jaolvera 

In the attached version (a macro-enabled workbook again), I have added code to the worksheet module that dynamically adjusts the minimum of the value axis when the Dashboard sheet is activated.