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 he

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.Row s)
Unrealized.Quote.Range ='SheetU'!$J$2:INDEX('SheetU'!$J:$J,Unrealized.Row s)
Unrealized.GLRunning.Range ='SheetU'!$M$2:INDEX('SheetU'!$M:$M,Unrealized.Row s)
Unrealized.PPS.Range ='SheetU'!$F$2:INDEX('SheetU'!$F:$F,Unrealized.Row s)

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