I recommend using R1C1 notation rather than A1 notation when creating
relative named ranges: its easier to see what you are doing.

Thats why Name Manager ( download from
http://www.decisionmodels.com/downloads.htm )
has a toggle option for R1C1/A1 on the main Name manager form and a filter
for Relative names.
In A1 mode Name Manager also shows you what cell the name is relative to.

for example
R1C:R10C refers to the current column rows 1:10
R1C[-3]:R10C[-3] refers 3 columns to the left of the current cell rows 1 :10

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"T. Valko" wrote in message
...
my main question is whether there is something inherent
to Named Ranges that can dynamically refer to a
relative range? Perhaps not.

It can be done but it's tricky and depends on the physical location of the
formula and the active cell location when you create the name.

For example:

Select cell D1
Defiine a named range as Rng which refers to A$1:A$10

This is how the named range Rng will be evaluated when the formula is
entered in certain cells:

A12: =SUM(Rng) Rng = IT$1:IT$10 (Excel versions prior to 2007)
D1: =SUM(Rng) Rng = A$1:A$10
E1: =SUM(Rng) Rng = B$1:B$10
H1: =SUM(Rng) Rng = E$1:E$10

Because the active cell was offset 3 columns to the right of the range of
interest, no matter where you enter the formula it will always refer to a
range that is 3 columns to the left of the cell that holds the formula.

This can be rather confusing so I recommend using some other method.

--
Biff
Microsoft Excel MVP


"Johnny_99" wrote in message
...
INDEX(Table,0,column_number)
=SUM(INDEX(Table,0,1)
=SUM(INDEX(Table,0,MATCH("Jan",A1:K1,0)))
=SUM(INDEX(Table,0,MATCH(X1,A1:K1,0)))

Thanks for the response. These are good suggestions for a Sum, however
I'm
struggling with trying to apply the "dynamic" Named Range for an Array
Sum.

For now, let's assume I used a Named Range for calculating Jan & Feb
sales:

={(Country="USA")*(Sales_Jan)} where Sales_Jan = $A$1:$A$99
={(Country="USA")*(Sales_Feb)} where Sales_Feb = $B$1:$B$99

Instead of having to have 12 distinct formulas (for 12 months), another
alternative would be to make the Sales Named Range cover 12 columns, then
narrow the Sum Array to select just the 1 column I want, such as:

={(Country="USA")*(Month="Jan")*(Sales)} where Sales =
$A$1:$K$99

and Month = $A$100:$K$100

Both of these options can work, but my main question is whether there is
something inherent to Named Ranges that can dynamically refer to a
relative
range? Perhaps not.

Thanks in advance,