Excel 2002 SP3
Win XP HE SP1

Hi,

I have a simple formula =AVERAGE(B2:B37)

The reference to Row 37 appears in many formulas throughout the
spreadsheet; depending on how much data I have in terms of periods; 1
year or 3 years or 10 years, etc.

Since each data point (that corresponds to a month or quarter, etc.) is
in its own row, the more periods (the longer time) I am looking at, the
more rows, etc.

Since cell Row 37 (or whatever other row) is a vital reference point for
many other formulas in my spreadsheet, how can I quickly change it to
reflect this addition in rows?

What I really mean is quickly change 37 to 87 for example so that the
formulas that were using B37 are now using B87--automatically, without
requiring manual change?

I don't think using Find and Replace is a good idea (even if it were to
work) as could create many other undesirable changes.

Thanks.

One way would be to enter the required row number in a cell - say D1 then
use:

=AVERAGE(B2:INDIRECT("B"&D1))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"stef" <(E-Mail Removed)> wrote in message
news:e$(E-Mail Removed)...
> Excel 2002 SP3
> Win XP HE SP1
>
> Hi,
>
> I have a simple formula =AVERAGE(B2:B37)
>
> The reference to Row 37 appears in many formulas throughout the
> spreadsheet; depending on how much data I have in terms of periods; 1 year
> or 3 years or 10 years, etc.
>
> Since each data point (that corresponds to a month or quarter, etc.) is in
> its own row, the more periods (the longer time) I am looking at, the more
> rows, etc.
>
> Since cell Row 37 (or whatever other row) is a vital reference point for
> many other formulas in my spreadsheet, how can I quickly change it to
> reflect this addition in rows?
>
> What I really mean is quickly change 37 to 87 for example so that the
> formulas that were using B37 are now using B87--automatically, without
> requiring manual change?
>
> I don't think using Find and Replace is a good idea (even if it were to
> work) as could create many other undesirable changes.
>
> Thanks.
>

Pick a cell, say E1 and put 37 in it.

=AVERAGE(INDIRECT("B2:B"&E1))

uses the value in E1 to make the range. If all your formulas use the cell
E1 rather than the constant 37, you only need to change that single cell to
change all the ranges
--
Gary''s Student - gsnu200730


"stef" wrote:

> Excel 2002 SP3
> Win XP HE SP1
>
> Hi,
>
> I have a simple formula =AVERAGE(B2:B37)
>
> The reference to Row 37 appears in many formulas throughout the
> spreadsheet; depending on how much data I have in terms of periods; 1
> year or 3 years or 10 years, etc.
>
> Since each data point (that corresponds to a month or quarter, etc.) is
> in its own row, the more periods (the longer time) I am looking at, the
> more rows, etc.
>
> Since cell Row 37 (or whatever other row) is a vital reference point for
> many other formulas in my spreadsheet, how can I quickly change it to
> reflect this addition in rows?
>
> What I really mean is quickly change 37 to 87 for example so that the
> formulas that were using B37 are now using B87--automatically, without
> requiring manual change?
>
> I don't think using Find and Replace is a good idea (even if it were to
> work) as could create many other undesirable changes.
>
> Thanks.
>

Nice, that works.

But it works for now forward (I am using it now).

What about using a spreadsheet built earlier and that contains lots of
separate sheets all with 37 (June 2000 for example) when we are now in
June 2007 and that row is 87?

How can I replace the reference to 37 with 87 automagically?

Sandy Mann wrote:
> One way would be to enter the required row number in a cell - say D1 then
> use:
>
> =AVERAGE(B2:INDIRECT("B"&D1))
>

Right, that's what I will do from now on, definitely.

I still have the problem of replacing existing work with the new
row--see my reply to Sandy

Gary''s Student wrote:
> Pick a cell, say E1 and put 37 in it.
>
> =AVERAGE(INDIRECT("B2:B"&E1))
>
> uses the value in E1 to make the range. If all your formulas use the cell
> E1 rather than the constant 37, you only need to change that single cell to
> change all the ranges

With cell C1 containing the row reference that othere cells would use

Perhaps this non-volatile formula:
=AVERAGE(B2:INDEX(B:B,$C$1))

Something like that would automatically adjust its references if rows or
columns were added.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"stef" wrote:

> Excel 2002 SP3
> Win XP HE SP1
>
> Hi,
>
> I have a simple formula =AVERAGE(B2:B37)
>
> The reference to Row 37 appears in many formulas throughout the
> spreadsheet; depending on how much data I have in terms of periods; 1
> year or 3 years or 10 years, etc.
>
> Since each data point (that corresponds to a month or quarter, etc.) is
> in its own row, the more periods (the longer time) I am looking at, the
> more rows, etc.
>
> Since cell Row 37 (or whatever other row) is a vital reference point for
> many other formulas in my spreadsheet, how can I quickly change it to
> reflect this addition in rows?
>
> What I really mean is quickly change 37 to 87 for example so that the
> formulas that were using B37 are now using B87--automatically, without
> requiring manual change?
>
> I don't think using Find and Replace is a good idea (even if it were to
> work) as could create many other undesirable changes.
>
> Thanks.
>

Actually, this doesn't quite work as I cannot copy and paste to
different columns as the B is acting like $B--anchored.
I need to be able to copy and transfer the column "B" to "C" etc.....

Gary''s Student wrote:
> Pick a cell, say E1 and put 37 in it.
>
> =AVERAGE(INDIRECT("B2:B"&E1))
>
> uses the value in E1 to make the range. If all your formulas use the cell
> E1 rather than the constant 37, you only need to change that single cell to
> change all the ranges

Stef,

I know that you said that you did not want to use REPLACE because it could
replace things that you did not want to replace but if you make the "Find
what:" the whole Reference:

(B2:B37)

and the "Replace with:"
(B2:B87)

Will you have other formulas that use that range that you don't want to
change?
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"stef" <(E-Mail Removed)> wrote in message
news:u$(E-Mail Removed)...
> Nice, that works.
>
> But it works for now forward (I am using it now).
>
> What about using a spreadsheet built earlier and that contains lots of
> separate sheets all with 37 (June 2000 for example) when we are now in
> June 2007 and that row is 87?
>
> How can I replace the reference to 37 with 87 automagically?
>
> Sandy Mann wrote:
>> One way would be to enter the required row number in a cell - say D1 then
>> use:
>>
>> =AVERAGE(B2:INDIRECT("B"&D1))
>>
>

Ron,
I like the idea but the B:B creates a circular ref in my case.


Ron Coderre wrote:
> With cell C1 containing the row reference that othere cells would use
>
> Perhaps this non-volatile formula:
> =AVERAGE(B2:INDEX(B:B,$C$1))
>
> Something like that would automatically adjust its references if rows or
> columns were added.
>
> Is that something you can work with?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "stef" wrote:
>
>> Excel 2002 SP3
>> Win XP HE SP1
>>
>> Hi,
>>
>> I have a simple formula =AVERAGE(B2:B37)
>>
>> The reference to Row 37 appears in many formulas throughout the
>> spreadsheet; depending on how much data I have in terms of periods; 1
>> year or 3 years or 10 years, etc.
>>
>> Since each data point (that corresponds to a month or quarter, etc.) is
>> in its own row, the more periods (the longer time) I am looking at, the
>> more rows, etc.
>>
>> Since cell Row 37 (or whatever other row) is a vital reference point for
>> many other formulas in my spreadsheet, how can I quickly change it to
>> reflect this addition in rows?
>>
>> What I really mean is quickly change 37 to 87 for example so that the
>> formulas that were using B37 are now using B87--automatically, without
>> requiring manual change?
>>
>> I don't think using Find and Replace is a good idea (even if it were to
>> work) as could create many other undesirable changes.
>>
>> Thanks.
>>

Ron, Cancel that--I had gotten distracted there for a moment. It
works... No circ ref.


stef wrote:
> Ron,
> I like the idea but the B:B creates a circular ref in my case.
>
>
> Ron Coderre wrote:
>> With cell C1 containing the row reference that othere cells would use
>>
>> Perhaps this non-volatile formula:
>> =AVERAGE(B2:INDEX(B:B,$C$1))
>>
>> Something like that would automatically adjust its references if rows
>> or columns were added.
>>
>> Is that something you can work with?
>> ***********
>> Regards,
>> Ron
>>
>> XL2002, WinXP
>>
>>
>> "stef" wrote:
>>
>>> Excel 2002 SP3
>>> Win XP HE SP1
>>>
>>> Hi,
>>>
>>> I have a simple formula =AVERAGE(B2:B37)
>>>
>>> The reference to Row 37 appears in many formulas throughout the
>>> spreadsheet; depending on how much data I have in terms of periods; 1
>>> year or 3 years or 10 years, etc.
>>>
>>> Since each data point (that corresponds to a month or quarter, etc.)
>>> is in its own row, the more periods (the longer time) I am looking
>>> at, the more rows, etc.
>>>
>>> Since cell Row 37 (or whatever other row) is a vital reference point
>>> for many other formulas in my spreadsheet, how can I quickly change
>>> it to reflect this addition in rows?
>>>
>>> What I really mean is quickly change 37 to 87 for example so that the
>>> formulas that were using B37 are now using B87--automatically,
>>> without requiring manual change?
>>>
>>> I don't think using Find and Replace is a good idea (even if it were
>>> to work) as could create many other undesirable changes.
>>>
>>> Thanks.
>>>