Dynamic chart range

[Peter Davison]

So back to the Chart.
I have data as below Excel Cells/Rows FA4 to (HZ9 - Random Column)
As there could be any number of stores (e.g. S011) at any time I wanted to make it dynamic.
I am using the following Offset and Counta as a named range (one for each row)
=OFFSET(Dashboard!$FA$6,0,0,COUNTA(Dashboard!$6:$6))
I'm sure I have the offset wrong, any help would be appreciated.
Thanks

	Total	Total		S011	S011	S011		S027	S027	S027
	Value%	Wastage%		Value%	LMtr%	Wastage%		Value%	LMtr%	Wastage%
Ambient	31%	10%		27%	42%	8%		34%	46%	10%
Confection	4%	0%		4%	9%	1%		4%	6%	0%
Drinks	23%	5%		24%	19%	5%		22%	22%	38%
Perishables	39%	81%		40%	30%	75%		39%	27%	51%

 

[RoryA]

I've moved this to its own thread as it was unrelated to your previous question.

I'm not clear what you want your formula to do. You're using COUNTA on an entire row, but using it to as the number of rows, not columns, that your range should include.

 

[arcaidius]

Can I ask a Question on This Thread if its Related? I have a dynamic Chart using:

=OFFSET(Sheetname!$C$2,COUNT(Sheetname!$C:$C),0,-13,1)

This gets the last 12 months of data from column C. works great!

Is there a formula that I can get the previous 12 months? so I can make another chart that compares both years?

 

[RoryA]

Subtract 12 from the result of the COUNT formula.