dynamic range in graph

[Rip1971]

I have a graph in my worksheet that has a dynamic amount of items on the x-axis.

How can i make it so that the range only looks at the filled in values instead of the whole range.
Chart data range =Template!$A$14:$A$170,Template!$CI$14:$CI$170,Template!$CK$14:$CL$170,Template!$CU$14:$CU$170,Template!$CY$14:$CY$170
Axis Label range =Template!$A$15:$A$170
Series values =Template!$CH$15:$CH$170

I think that the axis label range is the master of how far the range should go. that is where we fill in the values that need to go in the graph

If more info is needed let me know
thanks in advance

 

[Jeffrey Mahoney]

There are two ways I know of. Turn your data into an Excel Table, or create Dynamic Named Ranges.

Excel Tables Option
Convert you chart data into an excel table. Use the field names on the chart range reference like: =Table1[xRng] and Table1[yRng]. What is odd after you enter these into the chart series and edit them again, it will appear that it changed them to normal ranges. Don't worry, this is an Excel issue but it will expand and contract just like you Excel table.

Dynamic Named Ranges
Create a named using a formula like this: =OFFSET(Sheet1!$D$3,0,0,MATCH(1E+300,Sheet1!$D:$D)-2,1). I named mine xRng and yRng. In the Chart series enter the range like this: =Sheet1!xRng and =Sheet1!yRng. The formula above will count all the numerical values in the column. If your data starts in row 2 then change the formula like this: =OFFSET(Sheet1!$D$2,0,0,MATCH(1E+300,Sheet1!$D:$D)-1,1)

 

[Rip1971]

There are two ways I know of. Turn your data into an Excel Table, or create Dynamic Named Ranges.

Excel Tables Option
Convert you chart data into an excel table. Use the field names on the chart range reference like: =Table1[xRng] and Table1[yRng]. What is odd after you enter these into the chart series and edit them again, it will appear that it changed them to normal ranges. Don't worry, this is an Excel issue but it will expand and contract just like you Excel table.

Dynamic Named Ranges
Create a named using a formula like this: =OFFSET(Sheet1!$D$3,0,0,MATCH(1E+300,Sheet1!$D:$D)-2,1). I named mine xRng and yRng. In the Chart series enter the range like this: =Sheet1!xRng and =Sheet1!yRng. The formula above will count all the numerical values in the column. If your data starts in row 2 then change the formula like this: =OFFSET(Sheet1!$D$2,0,0,MATCH(1E+300,Sheet1!$D:$D)-1,1)

Jeffrey,
My knowlegde is not that great in excel learning fast thou. but somehow i cannot get those option to work.

I started from a macro and doing what I thought was what needed to happen,
the thing i thought was how can i make the range used in this small piece of code so it looks incolumn A find the last one in specific range (A6-A160) and adjust the range accordingly
the graph will then be updated after pressing button. or if maybe everytime workheet is update

Sub grafiek()

    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.SetSourceData Source:=range("Template!$A$5:$A$20,Template!$CB$5:$CG$20")

End Sub

sorry if my mind make things up

 

[Jeffrey Mahoney]

Did you turn your table into a proper Excel Table or did you try to use the dynamic named range?

 

[Rip1971]

Did you turn your table into a proper Excel Table or did you try to use the dynamic named range?

This is how my table looks.
Column BA is on the x-axis
the Y-axis is a percentage
All other columns give data which is placed in graph (see attached picture of graph)

if i hide not necessery rows the graph also hides those from the graph. which is more or less what i want.

Operation_Voortgang_Template_V5.xlsm
	BA	BB	BC	BD	BE	BF	BG
5	Equipment	Stop Procedure	Start Procedure	Gespoeld	Veiliggesteld (RLM)	Operation Commissioning	PRE-STARTUP
6	D820	0.5	0.5	0.5	0.5
7	D811	0.75	0.75
8	E822	1	0	1	1
9		1	1	1	1
10	E850	0	0	0	0
11
12
13
14
15
16
17
18
19	D811
20	D439 ( HL400 )
21	E810
22	D820
23
161
Template

Cell Formulas
Range	Formula
BA6:BA23,BA161	BA6	=IF(A6<>"",A6," ")
BB6:BB23,BB161	BB6	=IF(O6="Approved",1,IF(O6="in MOC",0.75,IF(O6="Bezig",0.5,IF(O6="N.V.T.",1,IF(O6<>"",0," ")))))
BC6:BC23,BC161	BC6	=IF(15="Approved",1,IF(P6="in MOC",0.75,IF(P6="Bezig",0.5,IF(P6="N.V.T.",1,IF(P6<>"",0," ")))))
BD6:BE23,BD161:BE161	BD6	=IF(Q6="Klaar",1,IF(Q6="Bezig",0.5,IF(Q6="N.V.T.",1,IF(Q6<>"",0," "))))
BF6	BF6	=IF(AA6="Klaar",1,IF(AA6="Bezig",0.5,IF(AA6="N.V.T.",1,IF(AA6<>"",0," "))))
BG6	BG6	=IF(AE6="Klaar",1,IF(AE6="Bezig",0.5,IF(AE6="N.V.T.",1,IF(AE6<>"",0," "))))