Named ranges with dependencies

[Dallman Ross]

I've started converting some charts I have whose data gets
updated very frequently so that the charts update their
data ranges automatically. For example, there is a
method outlined here:

http://www.j-walk.com/ss/excel/usertips/tip053.htm

That uses the "Name" feature (Insert / Name / Define)
to set up ranges that rely on COUNTA to see how long
the range is. Cool.

I'm interested in simplifying it further. I have many
charts with many series. They all rely on the same
(dynamic) length in the data range. So it seems kind
of silly to define a bunch of different names, all of
which are very similar except for the column number.

I now have stuff like this:

Name Value
Unrealized.Rows =COUNTA('Current CSV'!$A:$A)-2
Unrealized.DaysHeld.Range ='SheetU'!$N$2:INDEX('SheetU'!$N:$N,Unrealized.Rows)
Unrealized.Quote.Range ='SheetU'!$J$2:INDEX('SheetU'!$J:$J,Unrealized.Rows)
Unrealized.GLRunning.Range ='SheetU'!$M$2:INDEX('SheetU'!$M:$M,Unrealized.Rows)
Unrealized.PPS.Range ='SheetU'!$F$2:INDEX('SheetU'!$F:$F,Unrealized.Rows)

Then, in the chart, I have series such as one called "Gain/Loss
Running Tally" whose range is:

='SheetU'!Unrealized.GLRunning.Range

and so on. Works fine.


I want to have a Name or a user-defined function that lets me
state the column number when I invoke the function. Then I
could just have one repeating entry in the chart for each
series. The "Gain/Loss Running Tally", "Days Held", and
"Price Paid" series in my chart could just be, respectively,

='SheetU'!Unrealized.Range("M")
='SheetU'!Unrealized.Range("N")
='SheetU'!Unrealized.Range("F")

where the arguments are the column numbers. Then I wouldn't need
eight or so names for each type of chart I'm producing.

I don't know how to create such a user-defined function, however!
Help would be very much appreciated.

Dallman Ross

 

[Roger Govier]

Hi Dallman

Not sure whether this gets you any further, but I use a broadly similar
technique for defining ranges.

I have a Name Arng = Sheet1!$A$1:$Z$10000 or some suitably large size
to cope with the maximum range of data I am going to be dealing with.
I have lr (for Lastrow) using COUNTA() as you do to determine the last
used row of the sheet.
I have a List set up, which is a list of all of my column headings from
the sheet

Then for each named range, it is the same formula, just changing the
column Name e.g.
Analysis =
INDEX(ARng,1,MATCH("Analysis",List,0)):INDEX(Arng,lr,MATCH("Analysis",List,0))
Inv Date = INDEX(ARng,1,MATCH("Inv
Date",List,0)):INDEX(Arng,lr,MATCH("Inv Date",List,0))

It becomes a simple quick Copy and Paste of Defined Name into the one
formula to create each range.

 

[Dallman Ross]

In <[email protected]>, Roger Govier
<[email protected]> spake thusly:

Thanks, Roger. I actually also did think about using MATCH with
the column names to automate the named-range formulas further,
similarly to what you have done. I didn't implement it because
(a) I would have needed an hour or so of trial and error to get
it to work (and anyway, I just started all this today); and (b)
it still requires a bunch of similar named ranges rather than
one that could self-populate based on the argument. Hmm.
Nevertheless, I find your method pretty slick, and also feel
somewhat vindicated to find that I chose an approach that
turns out to be similar to yours.

Obtw: what's the advantage to having "List" with all the
column headers rather than matching on Row 1 itself?

Dallman

===============

 

[Roger Govier]

Hi Dallman

Obtw: what's the advantage to having "List" with all the
column headers rather than matching on Row 1 itself?

None really, except that I don't usually have my headers on Row 1 and, I
much prefer using named ranges to row or column references.
I nearly always leave 5 rows free at the top of any sheet I am setting
up, in case I want to use them for other things e.g. placing subtotals
there.

I then hide any rows that I am not using.
In reality my Arng's are $A$6:$Z$10000 but without going on to explain
all of the above, it was easier to answer using $A$1

 

[Dallman Ross]

Hi Dallman


None really, except that I don't usually have my headers on
Row 1 and, I much prefer using named ranges to row or column
references. I nearly always leave 5 rows free at the top of
any sheet I am setting up, in case I want to use them for other
things e.g. placing subtotals there.

Okay, I'm with you. Thanks very much for the good explanations
and help!

Dallman