URGENT: Using Named Ranges with Chart Wizard

[artisdepartis]

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... )

 

[Guest]

one way to do the (1) question is to use a pivot table as your source for the
chart

in (2)

=OFFSET(indirect(G1&"!$Q$1");0;0;5;COUNTA(Sheet1!$Q$1:$AZ$1))

 

[artisdepartis]

one way to do the (1) question is to use a pivot table as your source for the
chart

in (2)

=OFFSET(indirect(G1&"!$Q$1");0;0;5;COUNTA(Sheet1!$Q$1:$AZ$1))











- Show quoted text -

Hi BJ,

thx for the response! Gonna try it first thing tomorrow morning.
However, since this report was made by someone else... I don't know if
i can redo it all using PivotTables, without getting into extra work
over my head. (Deadline is Wednesday, which i'll make using the old
way, but i'd like a new way... ;-)

 

[Bernard Liengme]

Begin your quest here:
http://peltiertech.com/Excel/Charts/DynamicChartLinks.html
or
http://peltiertech.com/Excel/Charts/Dynamics.html#DynColCht
or
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
or
http://www.meadinkent.co.uk/xlgraphoffset.htm
or , for a simple example look at
http://people.stfx.ca/bliengme/ExcelTips/Dynamic.htm

Do come back if nothing helps
best wishes

 

[artisdepartis]

Begin your quest here:http://peltiertech.com/Excel/Charts/DynamicChartLinks.html
orhttp://peltiertech.com/Excel/Charts/Dynamics.html#DynColCht
orhttp://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
orhttp://www.meadinkent.co.uk/xlgraphoffset.htm
or , for a simple example look athttp://people.stfx.ca/bliengme/ExcelTips/Dynamic.htm

Do come back if nothing helps
best wishes
--
Bernard V Liengmewww.stfx.ca/people/bliengme
remove caps from email












- Show quoted text -

Dear Bernard,

already i got my first Chart functioning now. (Following one of ur
links). All I need to figure out now is to make the function a
'global', So I can call upon it on every sheet. (I have rearranged the
data on my 25 Sheets to have the same layout and format on every
sheet.)
I will continue looking, meanwhile just started doing it by hand ;-)

Best wishes, AdP