Named Range that uses "relative" range - Possible?

[Johnny_99]

BTW - Excel 2003.

I have some data in a table with a column for Jan (column A), Feb (column
B), etc. for Sales in Jan, Feb, etc. In the past, I had array formulas that
added up each month as needed, using an Array Sum, starting with Jan formula
were refer to A$1:A$99. As I copied this formula for Feb, the range I added
up was relative, so changed to B$1:B$99, etc.

Now I am trying to switch to some cleaner Named Ranges, such as Sales_Jan,
Sales_Feb, etc.). My add Array Sum formulas have been changed to use the
Named Ranges. Unformtunately, my formula for Jan uses Sales_Jan, while Feb
formula uses Sales_Feb, etc.

Is there some way I can use named ranges that are more "relative", so when I
copy it to other columns, the range used in the Named Range changes also? I
think this defeats the purpose of a "static" Named Range.

One concept could be to make the Named Range for Sales from $A$1:$K:99, then
add another dimension to the Array Sum to select which Columns I wish to
restrict the Array Sum to.

Perhaps this approach is more complicated than neccessary. Is there
something simpler out there?

 

[T. Valko]

One concept could be to make the Named Range

for Sales from $A$1:$K:99, then add another
dimension to the Array Sum to select which
Columns I wish to restrict the Array Sum to.

This is fairly easy to do.

Let's assume the whole range $A$1:$K$99 is named Table.

To reference specifc columns you would do something like this:

INDEX(Table,0,column_number)

For example:

=SUM(INDEX(Table,0,1)

Would sum A1:A99

Another way without having to hardcode the column number...

Let's assume the first row of Table are column headers that are the month
names as TEXT entries like Jan, Feb, Mar, etc.

=SUM(INDEX(Table,0,MATCH("Jan",A1:K1,0)))

Or, using a cell to hold the month name:

X1 = Jan

=SUM(INDEX(Table,0,MATCH(X1,A1:K1,0)))

 

[Johnny_99]

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,

 

[Harlan Grove]

BTW - Excel 2003. ....
Is there some way I can use named ranges that are more "relative", so when I
copy it to other columns, the range used in the Named Range changes also? I
think this defeats the purpose of a "static" Named Range.

....

One possibility would be putting the active cell anywhere in column A
and defining the name Sales referring to =A$1:A$99. Note the absence
of $ to the LEFT of the column letters. The formula =SUM(Sales)
entered in any cell in column A *OUTSIDE* of A1:A99 would be
equivalent to =SUM(A$1:A$99). But if you enter =SUM(Sales) in, say,
C101, that formula would be equivalent to =SUM(C$1:C$99).

 

[T. Valko]

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.

 

[Charles Williams]

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