Dragging a range
 

Hi All,

I have one formula saying =IF(SMALL(Range,3)=0,0,SUM(Range)). I named this
range by selecting 10 non adjacent cells in Row1. It works fine But.....If I
drag this formula down, it of course takes reference from the same rang i.e.
Row1. I want to be able to drag it down and want the ranges to change
accordingly.

For example- I selected A1,D1,G1,J1,M1 and named it as Range1. Now when I
drag it down, it should take references from A2,D2,G2,J2,M2.

Is it possible?

Thanks in advance.

Dragging a range
 

Use Insert | Name | Define
Locate the reference to Range
In the definition, change every instance of $1 to just 1; so $A$1 becomes
$A1
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Gaurav" wrote in message
...
Hi All,

I have one formula saying =IF(SMALL(Range,3)=0,0,SUM(Range)). I named this
range by selecting 10 non adjacent cells in Row1. It works fine But.....If
I drag this formula down, it of course takes reference from the same rang
i.e. Row1. I want to be able to drag it down and want the ranges to change
accordingly.

For example- I selected A1,D1,G1,J1,M1 and named it as Range1. Now when I
drag it down, it should take references from A2,D2,G2,J2,M2.

Is it possible?

Thanks in advance.

Dragging a range
 

Thanks Bernard. That works.

"Bernard Liengme" wrote in message
...
Use Insert | Name | Define
Locate the reference to Range
In the definition, change every instance of $1 to just 1; so $A$1 becomes
$A1
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Gaurav" wrote in message
...
Hi All,

I have one formula saying =IF(SMALL(Range,3)=0,0,SUM(Range)). I named
this range by selecting 10 non adjacent cells in Row1. It works fine
But.....If I drag this formula down, it of course takes reference from
the same rang i.e. Row1. I want to be able to drag it down and want the
ranges to change accordingly.

For example- I selected A1,D1,G1,J1,M1 and named it as Range1. Now when I
drag it down, it should take references from A2,D2,G2,J2,M2.

Is it possible?

Thanks in advance.