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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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"
<[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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"
> <[email protected]> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >>
> >>
> >>
>