Dynamic range name based on value in another row

[emmalc]

Hello,

I am working on a template for charting data. I want to be able to use a dynamic named ranges for series data so the chart is dynamic and I don't need to manually update the series data everytime. Sample number is x axis and Average Weights is the y axis. I made the dynamic range names based on the batch number. It is dynamic and if i change the batch number in one of the columns the appropriate datapoints change on the dynamic chart but it is plotting all empty data even though I have hidden/empty cells set to gaps.

Is it something wrong with my named ranges or with the chart data? (included the name manager for reference)

the end goal is to then have a template so the data entry can be done by someone without excel knowledge but they will still get the chart that they need.

Thank you in advance for your help!

 

[Skybluekid]

Try putting this into your dynamic range

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		Batch	1	1	1	1	1	1	2	2	2	2	3	3	3	3	3		1
3		Sample	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15		2
4		Min Weight	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23		3
5		Max Weight	27	27	27	27	27	27	27	27	27	27	27	27	27	27	27
6		Average Weights	25.84	25.84	25.88	25.92	25.87	25.92	25.97	25.88	25.94	25.91	25.88	25.87	25.92	25.94	25.88
7
8
9		1
10		Batch	1	1	1	1	1	1
11		Sample	1	2	3	4	5	6
12		Min Weight	23	23	23	23	23	23
13		Max Weight	27	27	27	27	27	27
14		Average Weights	25.84	25.84	25.88	25.92	25.87	25.92
15
Sheet4

Cell Formulas
Range	Formula
S2:S4	S2	=UNIQUE(TRANSPOSE(C2:Q2))
C10:H14	C10	=OFFSET($B$2,,MATCH(B9,$C$2:$Q$2,0),5,COUNTIF($C$2:$Q$2,B9))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B9	List	=$S$2#