Named Ranges in Excel Charts

[beakerboy]

I have a workbook with 12 identical worksheets. I’m wanting to do dynamic charting (Add a new row entry and the graph automagicaly updates) this requires replacing all range and cell references with defined names. I want to make it as straight-forward as I can.

One can define a named range such that every worksheet sees the exact same range (Range1=Sheet1!A1:A10) or so each sheet sees the range on it’s own sheet (Range1=!A1:A10). I did the latter so I had to do the work once, not 12 times. The problem now is I’d like to make a chart comparing Range1 vs time from Sheet 1 as well as Range1 vs time from Sheet2, etc. I was assuming that the formula Sheet1!Range1 would return Sheet1!A1:A10 but instead I receive an error (#NAME?). Any ideas if this is even possible? I CAN daccomplish the solution, but the only way I know for certain is to define Range1_1= Sheet1!A1:A10 Range1_2= Sheet2!A1:A10 Range1_3= Sheet3!A1:A10, and have the chart plot each named range vs. time. Defining 30 ranges per page on 12 pages will be a lot more work than I really want to do though.

Let me know if anything simpler comes to mind. Thanks,

Kevin Nowaczyk

 

[01Patrik]

You are getting the #NAME? error because your definition of RANGE1 is

=!A1:A10

There is no way to achieve what you want - a named range without reference to the sheet.


But you can actually achieve dynamic charting without using named ranges (using direct references like A1:A10), as long as new posts are inserted inside the range, not in the end of it.

A way of doing this would be to use ADDRESS in combination with COUNTA:

=indirect(A2&"!A1:"&address(counta($A$1:$A$15),1,4))

that returns

"Sheet1!A1:A10"

until you add something in A11, then it returns

"Sheet1!A1:A11"

I'm not certain if the chart updates correctly, though

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.