automatically replace cell in formula

[stef]

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.

 

[Sandy Mann]

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 address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

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]

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?

 

[stef]

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

 

[Guest]

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]

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.....

 

[Sandy Mann]

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 address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[stef]

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

 

[stef]

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

 

[stef]

Ron, btw, what is non-volatile versus volatile?

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

 

[Guest]

Hi, Stef

I'm glad I could help. Thanks for letting me know.

Regarding "volatile functions":
In general, Excel is optimized to only recalc the cells whose returned
values are affected by changes in worksheet data that impacts them.

However, there is a short list of functions that recalculate their values
whenever any cell in the workbook recalculates. In most cases, the impact on
calculation time is minimal. But, if your workbook makes generous use of
these functions the workbook can become sluggish.

Some volatile functions are obvious, like the NOW() function.
Others, not so much.

The list is slightly different, depending on your version of Excel, but this
is a good base: RAND(), NOW(), TODAY(), OFFSET(), CELL(), INDIRECT()

I hope that answers your question.

***********
Regards,
Ron

XL2002, WinXP

 

[stef]

Yes, thanks a lot Ron, very helpful.

And thanks to Sandy and Gary as well