Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Dynamic Ranges
Hello,
I need to develop a function which uses a range. The range is dynamic based upon a Lease Term. Example: I have a 5 year lease - The range will encompase 5 cells (A1:E1). I will always know the starting Cell. Any help would be much appreciated. thanks ACase |
#2
|
|||
|
|||
Not sure i understand the question - what do you want to know?
do you want to know how to develop a function that uses a range - if so, any particular function do you want to know how to create a dynamic range - if so, check out dynamic ranges at www.contextures.com/tiptech.html Cheers JulieD "ACase" wrote in message ... Hello, I need to develop a function which uses a range. The range is dynamic based upon a Lease Term. Example: I have a 5 year lease - The range will encompase 5 cells (A1:E1). I will always know the starting Cell. Any help would be much appreciated. thanks ACase |
#3
|
|||
|
|||
Thanks JulieD,
Let me better explain my problem. I have a formula. =NPV(F75,I73:S73)+G73 The range in this formula I73:S73 covers 11 cells. The 11 represents an 11 Year lease. I do not want the user to adjust the range every time we calculate a new lease. (Some leases are for 2 years some are for 10 years). How can I modify this formula so that the ending cell in the range will be based upon the number of Lease Years. Example: IF this were a 10 year lease I would want the formula to dynamically adjust itself accounting for only the values in the cell range I73:R73.. Thanks Again "JulieD" wrote: Not sure i understand the question - what do you want to know? do you want to know how to develop a function that uses a range - if so, any particular function do you want to know how to create a dynamic range - if so, check out dynamic ranges at www.contextures.com/tiptech.html Cheers JulieD "ACase" wrote in message ... Hello, I need to develop a function which uses a range. The range is dynamic based upon a Lease Term. Example: I have a 5 year lease - The range will encompase 5 cells (A1:E1). I will always know the starting Cell. Any help would be much appreciated. thanks ACase |
#4
|
|||
|
|||
You can use the offset function to define the range
the following page shows how to define a dynamic range for charts http://www.peltiertech.com/Excel/Charts/Dynamics.html It should work as well for your purposes Hope this helps Bob On Fri, 25 Mar 2005 07:49:09 -0800, "ACase" wrote: Thanks JulieD, Let me better explain my problem. I have a formula. =NPV(F75,I73:S73)+G73 The range in this formula I73:S73 covers 11 cells. The 11 represents an 11 Year lease. I do not want the user to adjust the range every time we calculate a new lease. (Some leases are for 2 years some are for 10 years). How can I modify this formula so that the ending cell in the range will be based upon the number of Lease Years. Example: IF this were a 10 year lease I would want the formula to dynamically adjust itself accounting for only the values in the cell range I73:R73.. Thanks Again "JulieD" wrote: Not sure i understand the question - what do you want to know? do you want to know how to develop a function that uses a range - if so, any particular function do you want to know how to create a dynamic range - if so, check out dynamic ranges at www.contextures.com/tiptech.html Cheers JulieD "ACase" wrote in message ... Hello, I need to develop a function which uses a range. The range is dynamic based upon a Lease Term. Example: I have a 5 year lease - The range will encompase 5 cells (A1:E1). I will always know the starting Cell. Any help would be much appreciated. thanks ACase |
#5
|
|||
|
|||
Use of Offset Function:
If I had numbers in Cell B15:H15 In Cell D4 I enter 5 Then in Cell D6 I enter =SUM(B15:OFFSET(B15,0, D4-1)) I would get the Sum of Cells B15,C15,D15, E15 and F15. ( Only 5 -- according to Cell D4). HTH "Bob Tarburton" wrote in message ... You can use the offset function to define the range the following page shows how to define a dynamic range for charts http://www.peltiertech.com/Excel/Charts/Dynamics.html It should work as well for your purposes Hope this helps Bob On Fri, 25 Mar 2005 07:49:09 -0800, "ACase" wrote: Thanks JulieD, Let me better explain my problem. I have a formula. =NPV(F75,I73:S73)+G73 The range in this formula I73:S73 covers 11 cells. The 11 represents an 11 Year lease. I do not want the user to adjust the range every time we calculate a new lease. (Some leases are for 2 years some are for 10 years). How can I modify this formula so that the ending cell in the range will be based upon the number of Lease Years. Example: IF this were a 10 year lease I would want the formula to dynamically adjust itself accounting for only the values in the cell range I73:R73.. Thanks Again "JulieD" wrote: Not sure i understand the question - what do you want to know? do you want to know how to develop a function that uses a range - if so, any particular function do you want to know how to create a dynamic range - if so, check out dynamic ranges at www.contextures.com/tiptech.html Cheers JulieD "ACase" wrote in message ... Hello, I need to develop a function which uses a range. The range is dynamic based upon a Lease Term. Example: I have a 5 year lease - The range will encompase 5 cells (A1:E1). I will always know the starting Cell. Any help would be much appreciated. thanks ACase |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect and dynamic ranges | Excel Worksheet Functions | |||
dynamic ranges | Excel Worksheet Functions | |||
Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |