The formula for the chart values would be similar to:

=OFFSET(Sheet1!$B$1,1,0,1,COUNTA(Sheet1!$2:$2)-1)

and for chart labels:

=OFFSET(Sheet1!ChartValues,-1,0)

bobf wrote:
I successfully used Debra Dalgleish's method to create a
dynamic chart where my data was in columns. When I try to
apply the same approach to data that is ROWS, it doesn't
work. In fact my x axis labels should be Monday, Tuesday,
Wednesday, etc, but on the chart they are Monday, 4.6,
space, space, June 24 etc as it is looking downwards
rather than across. Any ideas, anyone. Thanks.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html