If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Reference to a dynamic range
Hi,
Is there a simple/elegant way to refer to a range that is constantly changes? For example if I have a sheet that holds some data in a range and it is being constantly updated by adding rows or removing rows, is there a way to refer to the current range without the need to change the formulas every time a row is being added or removed? |
#2
|
|||
|
|||
Hi Yossi,
The simplest way would be to use range names - this way you only need to re-define the range name to update the formule. Let's say you have a list on one sheet, and a Pivot Table on the other. Rather than having to repoint the Pivot Table at the whole new range, if the Pivot Table was just based on range 'PivotData', all you would have to do would be to update the definition of the 'PivotData' range. This can be done manually - select the block, and InsertNameDefine, or you can use the macro below (adjust as necessary). Sub SetRange() Sheets("Sheet1").Select Range("A5").Select Selection.CurrentRegion.Select ActiveWorkbook.Names.Add Name:="rangename2", RefersToR1C1:=Selection.CurrentRegion End Sub Hope this helps Sunil Jayakumar "Yossi" wrote in message ... Hi, Is there a simple/elegant way to refer to a range that is constantly changes? For example if I have a sheet that holds some data in a range and it is being constantly updated by adding rows or removing rows, is there a way to refer to the current range without the need to change the formulas every time a row is being added or removed? www.ayyoo.com/loans.html |
#3
|
|||
|
|||
See the advice at this link
http://www.contextures.com/xlNames01.html#Dynamic "Yossi" wrote: Hi, Is there a simple/elegant way to refer to a range that is constantly changes? For example if I have a sheet that holds some data in a range and it is being constantly updated by adding rows or removing rows, is there a way to refer to the current range without the need to change the formulas every time a row is being added or removed? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 08:51 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 04:44 PM |
dynamic range help | drofnats | Worksheet Functions | 1 | June 22nd, 2004 12:19 AM |
Dynamic Range and Combo Box | Stephen Glynn | New Users | 1 | June 4th, 2004 06:11 PM |