Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Named Formula Weakly Interacts with a Variable Range on the Worksheet - Re-Visit
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Named Formula Weakly Interacts with a Variable Range on the
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 " wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Named Formula Weakly Interacts with a Variable Range on the
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Named Formula Weakly Interacts with a Variable Range on the
On Sep 5, 1:21 pm, 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Named Formula Weakly Interacts with a Variable Range on
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 " wrote: On Sep 5, 1:21 pm, 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Named Formula Weakly Interacts with a Variable Range on
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting a named range to appear in another worksheet | Excel Discussion (Misc queries) | |||
Variable names for named range | Excel Discussion (Misc queries) | |||
Removing `named range´ from worksheet | Excel Discussion (Misc queries) | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
Using Named Range with protection on the worksheet | Excel Discussion (Misc queries) |