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

1. Enter an Excel WorkSheet. This time, the named formula is created
in the Define Name space,

TitleCount = SUM(IF(MID(
OFFSET('Sheet1'!WorkArea,0,4,ROWS('Sheet1'!E$2:E6) ,1),1,100)=
MID('Sheet1'!E6,1,100),1,0))

WorkArea is defined a range of A2:G100 on the WorkSheet. TitleCount is
applied in Cell B6.

2. And, the given formula works fine until it's so required to insert
a column between Column 2 and 6 of the WorkSheet ; and similarly, it's
anticipated that there would be more columns added thereof in the
foreseeable future.

3. Then, it behooves revising the formula ; consequently, the affected
part is essentially that of altering the OFFSET function such as
(replacing the figure 4, meant to be Column E, with alternative
expression),

OFFSET('Sheet1'!WorkArea,0,COLUMNS('Sheet1'!A6:E6)-1,ROWS('Sheet1'!E
$2:E6),1),1,100)

4. Testing follows suit by inserting a column at Column C and needless
to say, columns to the right are shifted to follow the prevalent
alphabetical order of the Worksheet.

5. Then, Cell B6 is selected ; Go to Define Name space. Upon closer
examination,

COLUMNS('Sheet1'!A6:E6), remains as is, albeit expecting that to be
updated thus,
COLUMNS('Sheet1'!A6:F6).

6. Needless to say and there too,

ROWS('Sheet1'!E$2:E6) and MID('Sheet1'!E6,1,100),

remain unmoved by the changing state of the WorkSheet.

7. But still, inserting a row at Row 6 of the WorkSheet will instigate
an interaction such that the range in the Define Name space will be
updated like, E6 to become E7.

8. This is Excel 2000. Please share your experience. Regards.