Excel Named Formula Weakly Interacts with a Variable Range on the Worksheet

[tkt_tang]

1. An Excel Named Formula incorporates a worksheet range say,
$A$1:$A$60, as a part of the formula.

2. When the given range on the worksheet expands by virtue of inserting
rows thereof (such that $A$1:$A$60 becomes $A$1:$A$70), all the
worksheet formulae will respond by adjusting themselves to suit the new
state of the worksheet.

3. However, the named formula will not self-adjust the range (namely,
$A$1:$A$60 remains as is) in the event of the changing state on the
worksheet. Needless to say, named formulae would react in many other
ways as expected.

4. Please devise a means such that a named formula would react
correspondingly to the variable states of the worksheet.

5. Please share your experience. Regards.

 

[Arvi Laanemets]

Hi

Use named ranges defined dynamically. A simpliest example:
MyRange=OFFSET(Sheet1!$A$1,,,COUNTA($A:$A),1)

 

[tkt_tang]

1. But, there are more calculations taking place outside the range
$A$1:$A$70 and thereabouts.

2. A dynamic range such as,
MyRange=OFFSET(Sheet1!$A$1,,,COUNTA($A:$A),1), is likely to cause
interferences here and there eventually when the worksheet grows with
its range of utilization.

3. Regards.

 

[Bob Phillips]

If I understand what you are saying, then I disagree with your conclusions.

I created a named formula, Test, with a RefersTo value of

=IF(Sheet1!$A$1:$A$10>1,Sheet1!$A$1:$A$10)

In another cell I added =SUM(Test) and got the answer I expected.

I then inserted a row at row 5, added a value of 5 in A5, and my SUM amount
increased by 5. Checking the named formula, the range had expanded.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Niek Otten]

In my Excel2003 this works perfectly; the range in the named formula is
adjusted.

Maybe you can use a defined name in the definition of the named formula.

 

[Niek Otten]

You didn't give us your formula. If the range was an argument to the
INDIRECT() function, it would not be updated. But that would be the case in
a worksheet formula too.
Maybe you should post your formula.

 

[tkt_tang]

1. Thank you for your response to my query.

2. The reference range, as shown, was !$A$1:$A$60.

3. The worksheet name was inadvertently removed and hence causing the
(affected) Named Formula to be decoupled from the changing states of
the worksheet.

4. The reference range, by correction, should indeed be
Sheet1!$A$1:$A$60 (then, the interaction is reinstated).

5. Regards.