Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel range selection as if dragging, keyboard not responding | New Users to Excel | |||
Dragging | Excel Worksheet Functions | |||
Dragging range (array) | Excel Discussion (Misc queries) | |||
dragging | Excel Discussion (Misc queries) | |||
Dragging Across | Excel Discussion (Misc queries) |