Forum Discussion
auto adjusting axis based on cell ranges
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?
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.
I uploaded the excel file because I got stuck at the defining portion, the two worksheets are titled "person diagram" and "person graph"
I don't see an attachment or a link.
