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

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

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

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

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