Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
URGENT: Using Named Ranges with Chart Wizard
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... ) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
URGENT: Using Named Ranges with Chart Wizard
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" wrote: 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... ) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
URGENT: Using Named Ranges with Chart Wizard
On Jul 2, 4:46 pm, bj wrote:
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" wrote: 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... )- Hide quoted text - - 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... ;-) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
URGENT: Using Named Ranges with Chart Wizard
Begin your quest he
http://peltiertech.com/Excel/Charts/...hartLinks.html or http://peltiertech.com/Excel/Charts/...html#DynColCht or http://www.tushar-mehta.com/excel/ne...rts/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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "artisdepartis" wrote in message oups.com... 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... ) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
URGENT: Using Named Ranges with Chart Wizard
On Jul 2, 4:52 pm, "Bernard Liengme"
wrote: Begin your quest hehttp://peltiertech.com/Excel/Charts/...hartLinks.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 "artisdepartis" wrote in message oups.com... 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... )- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using named 3-D ranges | Excel Discussion (Misc queries) | |||
Named Ranges | Excel Worksheet Functions | |||
Urgent! Prob with Excel 2002 Template Wizard with Data Tracking ad | Excel Discussion (Misc queries) | |||
Conditional Sum Wizard with dynamic named ranges | Excel Worksheet Functions | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) |