| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I stop 2nd row deletion
I have a data sheet with a header row. DATA1
All my calculations in other sheets refer to rangeNames that start with row2 in the DATA1 sheet. I have found that any row can be deleted from the DATA1 sheet BUT if row 2 deleted then the calculations fail. I would like code that would allow row 2 fields to be edited or corrected with PASTE Values but NOT allow row2 to be deleted. Any ideas? Dennis |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I stop 2nd row deletion
Define your names in terms of the first row. For example
Name: MyName RefersTo: =OFFSET(DATA1!$A$1,1,0,COUNTA(DATA1!$A:$A)-1,1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "ssGuru" wrote in message ups.com... I have a data sheet with a header row. DATA1 All my calculations in other sheets refer to rangeNames that start with row2 in the DATA1 sheet. I have found that any row can be deleted from the DATA1 sheet BUT if row 2 deleted then the calculations fail. I would like code that would allow row 2 fields to be edited or corrected with PASTE Values but NOT allow row2 to be deleted. Any ideas? Dennis |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I stop 2nd row deletion
On Sep 17, 9:54 am, "Jon Peltier"
wrote: Define your names in terms of the first row. For example Name: MyName RefersTo: =OFFSET(DATA1!$A$1,1,0,COUNTA(DATA1!$A:$A)-1,1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ "ssGuru" wrote in message ups.com... I have a data sheet with a header row. DATA1 All my calculations in other sheets refer to rangeNames that start with row2 in the DATA1 sheet. I have found that any row can be deleted from the DATA1 sheet BUT if row 2 deleted then the calculations fail. I would like code that would allow row 2 fields to be edited or corrected with PASTE Values but NOT allow row2 to be deleted. Any ideas? Dennis- Hide quoted text - - Show quoted text - Thanks Jon that is what I had done for a number of ranges using INDEX rather than Offset which started the column range name at row 2 down to how ever many records were in Data1, thus ignoring the header row. For example Say "RangeNameH": =Calc1!$H$2:INDEX(Calc1!$H: $H,COUNTA(Data1!$A:$A)) Works great in SUMPRODUCT formulas and such. Maybe if I start at row 1 (=Calc1!$H$1:INDEX....) which is the header row then I could delete row2 without any problem?? But then I am concerned that the header row will be confused in the calculations in some way. Can you elaborate on the efficacy of using INDEX or OFFSET when defining range names that will be used in calculations? Will one allow the deletion of the first row while another won't? Thanks, Dennis |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I stop 2nd row deletion
On Sep 17, 1:32 pm, ssGuru wrote:
On Sep 17, 9:54 am, "Jon Peltier" wrote: Define your names in terms of the first row. For example Name: MyName RefersTo: =OFFSET(DATA1!$A$1,1,0,COUNTA(DATA1!$A:$A)-1,1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ "ssGuru" wrote in message oups.com... I have a data sheet with a header row. DATA1 All my calculations in other sheets refer to rangeNames that start with row2 in the DATA1 sheet. I have found that any row can be deleted from the DATA1 sheet BUT if row 2 deleted then the calculations fail. I would like code that would allow row 2 fields to be edited or corrected with PASTE Values but NOT allow row2 to be deleted. Any ideas? Dennis- Hide quoted text - - Show quoted text - Thanks Jon that is what I had done for a number of ranges using INDEX rather than Offset which started the column range name at row 2 down to how ever many records were in Data1, thus ignoring the header row. For example Say "RangeNameH": =Calc1!$H$2:INDEX(Calc1!$H: $H,COUNTA(Data1!$A:$A)) Works great in SUMPRODUCT formulas and such. "...Maybe if I start at row 1 (=Calc1!$H$1:INDEX....) which is the header row then I could delete row2 without any problem?? But then I am concerned that the header row will be confused in the calculations in some way..." That won't work I found. I tried to change the Ranges to include the header row and all my SUMPRODUCT calculations failed. So I have to start the OFFSET or INDEX range with row2 and then deletion of row2 looses the start and all calculations endup with REF and VALUE errors. The calcs are happy with the deletion of any row beyond row2. I still need code that will PREVENT deletion of row2 but won't stop editing or pasting data in the fields. Thanks, Dennis |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I stop 2nd row deletion
Well, OFFSET is certainly easier, but you can use INDEX and INDIRECT
together. This gives you the range from A2 to the lowest cell in B (actually from B2 to the lowest cell in A): =INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)):INDIRECT ("$B$2") If you delete any single row, you're still fine (unless you delete row 2 and it's the only row of data below the headers). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "ssGuru" wrote in message ups.com... On Sep 17, 1:32 pm, ssGuru wrote: On Sep 17, 9:54 am, "Jon Peltier" wrote: Define your names in terms of the first row. For example Name: MyName RefersTo: =OFFSET(DATA1!$A$1,1,0,COUNTA(DATA1!$A:$A)-1,1) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ "ssGuru" wrote in message oups.com... I have a data sheet with a header row. DATA1 All my calculations in other sheets refer to rangeNames that start with row2 in the DATA1 sheet. I have found that any row can be deleted from the DATA1 sheet BUT if row 2 deleted then the calculations fail. I would like code that would allow row 2 fields to be edited or corrected with PASTE Values but NOT allow row2 to be deleted. Any ideas? Dennis- Hide quoted text - - Show quoted text - Thanks Jon that is what I had done for a number of ranges using INDEX rather than Offset which started the column range name at row 2 down to how ever many records were in Data1, thus ignoring the header row. For example Say "RangeNameH": =Calc1!$H$2:INDEX(Calc1!$H: $H,COUNTA(Data1!$A:$A)) Works great in SUMPRODUCT formulas and such. "...Maybe if I start at row 1 (=Calc1!$H$1:INDEX....) which is the header row then I could delete row2 without any problem?? But then I am concerned that the header row will be confused in the calculations in some way..." That won't work I found. I tried to change the Ranges to include the header row and all my SUMPRODUCT calculations failed. So I have to start the OFFSET or INDEX range with row2 and then deletion of row2 looses the start and all calculations endup with REF and VALUE errors. The calcs are happy with the deletion of any row beyond row2. I still need code that will PREVENT deletion of row2 but won't stop editing or pasting data in the fields. Thanks, Dennis |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| #Ref! after row deletion | Excel Discussion (Misc queries) | |||
| name deletion | Excel Discussion (Misc queries) | |||
| How to stop sheet deletion | Excel Programming | |||
| How do I stop other circles in other cells to stop selecting? | Excel Worksheet Functions | |||
| How do I stop other circles in other boxes to stop selecting? | Excel Worksheet Functions | |||
Linear Mode
