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

[tkt_tang]

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.

 

[OssieMac]

Hi,

If I understand correctly then your concern is what happens to named ranges
when columns or rows are inserted or deleted. See if the following helps.

Named areas:-

Expand or reduce by the number of columns or rows inserted or deleted in the
middle of the named range.

Move to the right or left by the number of columns inserted or deleted to
the left of the named range.

Move down or up by the number of columns inserted or deleted above the named
range.

In VBA if you want to preserve the named range from the existing first to
last cells irrespective of columns being inserted and/or deleted, then prior
to insertion or deletion of columns and/or rows, then save the address of the
existing first and last cell of the named range and then rename the range
after inserting and/or deleting rows.

The following VBA example demonstrates the above. Copy it into a blank
workbook and try it out:-

Sub Named_Areas()

Dim strFirst As String
Dim strLast As String

'Create a dummy named range to work with.
Sheets("Sheet1").Select
ActiveWorkbook.Names.Add Name:="WorkArea", RefersToR1C1:= _
"=Sheet1!R2C1:R100C7"


'Save the address of first and last cells of _
WorkArea to variables
With Sheets("Sheet1").Range("WorkArea")
strFirst = .Cells(1).Address
strLast = .Cells(.Cells.Count).Address
End With

Range("WorkArea").Select
MsgBox "Named area address before column insertion " _
& Sheets("Sheet1").Range("WorkArea").Address
Range("A1").Select

'Insert columns
Columns("C:E").Insert Shift:=xlToRight

Range("WorkArea").Select
MsgBox "Named area address after column insertion " _
& Sheets("Sheet1").Range("WorkArea").Address
Range("A1").Select


'Rename the work are to the original addresses
ActiveWorkbook.Names.Add Name:="WorkArea", _
RefersToR1C1:=Range(strFirst, strLast)

Range("WorkArea").Select
MsgBox "Named area address after renaming WorkArea " & _
Sheets("Sheet1").Range("WorkArea").Address
Range("A1").Select


End Sub


Regards,

OssieMac

 

[tkt_tang]

Mr. OssieMac,

Thank you for responding to the query.

My concern is that named ranges (created in Define Name space) should
reflect the prevalent states of the worksheet when the corresponding
columns or rows are inserted or deleted thereof.

In this case, the interaction linkage between worksheet and Define
Name space is akin to Swiss Cheese ; there are holes in the whole
thingy and hence weakening the interaction process.

Regards.

 

[tkt_tang]

On Sep 5, 1:21 pm, (e-mail address removed) wrote:

Mr. OssieMac

Thank you for responding to the query.

My concern is that named ranges (created in Define Name space) should
reflect the prevalent states of the worksheet when the corresponding
columns or rows are inserted or deleted thereof.

In this case, the interaction linkage between worksheet and Define
Name space is akin to Swiss Cheese ; there are holes in the whole
thingy and hence weakening the interaction process.

Would like to add that,

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

Since the given ranges of the above formula do not reflect the
prevalent states of the worksheet when the corresponding columns (such
as would be taking place within the Range of E$2:E6) are inserted or
deleted thereof,

Consequently, TitleCount would not compute correctly.

Is there any alternative means to work around this difficulty ?

Please share your experience.

Regards.

 

[OssieMac]

I am not sure that I fully understand what you are trying to achieve. Can you
answer the following questions so that I can get a firm understanding of what
you are trying to explain:-

1. What is the initial range of WorkArea prior to inserting columns?
2. How many columns you insert? (Make up a number if it varies)
3. What is the range of WorkArea after you insert the columns?
4. What range would you like WorkArea to be after inserting the columns?

Regards,

OssieMac

 

[tkt_tang]

Mr. OssieMac,

1. What is the initial range of WorkArea prior to inserting columns?

Answer : WorkArea is defined an initial range of A2:G100 on the
WorkSheet

2. How many columns you insert? (Make up a number if it varies)

Answer : For the purpose of testing the given formula, I have inserted
merely 1 column (to the right of Column B) and thereof, the formula
fails to compute correctly (albeit it works fine prior to inserting
that column).

The named formula created in the Define Name space is repeated for
ease reference,

TitleCount = SUM(IF(MID(
OFFSET('Sheet1'!WorkArea,0,
COLUMNS('Sheet1'!A6:E6)-1,
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.
May insert several more columns (to the right of Column B) as the need
arises.

3. What is the range of WorkArea after you insert the columns?

Answer : After inserting 1 column (to the right of Column B), WorkArea
expands by 1 column to follow suit (namely, A2:H100 just as would be
expected following the logical operations of Excel)

4. What range would you like WorkArea to be after inserting the
columns?

Answer : After inserting the columns (to the right of Column B),
WorkArea would consequently be, by incremental expansion, A2:H100 to
I100 to J100 to K100 etc.

Regards.