Home |
Search |
Today's Posts |
#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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range for Function (Vlookup etc) | Excel Worksheet Functions | |||
Dynamic Print Range Help | Excel Worksheet Functions | |||
Add up a Dynamic Range with 2 Variables | Excel Worksheet Functions | |||
Add a Dynamic Range with 2 Conditions Q | Excel Worksheet Functions | |||
How do I reference and sort a range of numbers in Excel 97? | Excel Worksheet Functions |