URGENT question (1): Is it possible to use a Named Range as a source
for a Chart? If so, how?

I have done this succesful with PivotTable and am now trying to get it
to work with Charts. The idea is to use a dynamic range as a source
for the chart.

The Problem: when I enter a Named Range asthe Data Range of my chart,
Excel converts this to a static formula.
Example:

=OFFSET(Sheet1!$Q$1;0;0;5;COUNTA(Sheet1!$Q$1:$AZ$1 ))
Turns into
=Sheet1!$Q$1:$AC$5 (last col with data is indeed AC...)

PLEASE HELP! THX

Less urgent question (2):
I have the names of my sheets in a list. How can if refer to these
using this dynamic range?
=OFFSET(Sheet1!$Q$1;0;0;5;COUNTA(Sheet1!$Q$1:$AZ$1 ))
Where the name of Sheet1 is in cell($G$1)

(I got about 100 Charts to update on 50 Sheets, that's why im trying
these methods... )