Graphs using indirect addressing

  • Thread starter Thread starter tkpmep
  • Start date Start date
T

tkpmep

I have a spreadsheet with a large number of graphs that are driven by
named ranges that need to be modified occasionally. The named ranges
have the following general form:

Dates1 = OFFSET('Time Series'!$C$3,0,0,COUNT('Time Series'!$C$3:$C
$2500),1)
Dates2 = OFFSET('Time Series'!$C$1500,0,0,COUNT('Time Series'!$C
$1500:$C$2500),1)
Return1= OFFSET('Time Series'!$D$3,0,0,COUNT('Time Series'!$C$3:$C
$2500),1)
Return2= OFFSET('Time Series'!$D$1500,0,0,COUNT('Time Series'!$C
$1500:$C$2500),1)

The Graphs are now driven off these named ranges. A typical graph
series looks like this:
=SERIES("Return 1 for Entire Period", 'History.xls'!Dates1,
'History.xls'!Return1,1)

Thanks to the COUNT in the formulas, the graph is always sized
correctly - blank cells are ignored.

Here's my problem - when I want to change the ranges (for example,
change $C$1500 in Dates2 to $C$2000), I have to go through each named
range in the Insert>Name>Define Name dialog and manually edit it.
There's got to be a better way.

I tried writing all the formulas for the named ranges on a separate
sheet (so that I could use search and replace to quickly change all 40
of them) and then using the INDIRECT function as follows, but it did
not work:

In a new sheet called Names, I wrote the formula for Dates1 in cell A1
with double quotes around it to make it a string i.e.
="OFFSET('Time Series'!$C$3,0,0,COUNT('Time Series'!$C$3:$C$2500),1)"
and then modified the graph series to read
=SERIES("Return 1 for Entire Period", indirect('Names'!A1),
'History.xls'!Return1,1)

Is my approach fundamentally flawed? Does indirect addressing work at
all with graphs?

Alternatively, Is there a way to edit all the named ranges without
going through each one manually in the Insert>Name>Define Name dialog?

Thanks in advance

Thomas Philips
 
Why not just use

COUNT('Time Series'!$C:$C)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
A series formula may only contain arguments which resolve to ranges, whether
cell addresses or references to Names. You cannot do any computation in a
series formula.

You could use a nested Offset function:

Dates2 = OFFSET('Time Series'!$C$1500,0,0,COUNT(OFFSET('Time
Series'!$C$1,1499,0,1001,1)),1)

then replace hardcoded values 1499 and 1001 with references to cells which
hold the desired values:

Dates2 = OFFSET('Time Series'!$C$1500,0,0,COUNT(OFFSET('Time
Series'!$C$1,'Time Series'!$A$1,0,'Time Series'!$A$2,1)),1)

Link all of your Name definitions to these cells, so you have to make
subsequent changes only once.

- Jon
 
John,

Thanks a mill. Yes, there some tedium the first time around, but in
the long term I'll be a much happier camper thanks to your solution.

Sincerely

Thomas Philips
 
Back
Top