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.

--
Regards

Roger Govier


"Dallman Ross" <dman@localhost. wrote in message
...
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