Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference Source Deleted & VBA ref adjustments
Is there a way to set a formula so that if the row that is referenced is
deleted the formula automatically adjusts to the row that takes its place. I tried an absolute reference but still get the #REF! error. Specifically the formula in cell B12 on sheet 2 refers to cell J13 on sheet 1. If I delete row 13 on sheet 1 I would like cell B12 to now refer to the cell that was formally J14 but has now moved up into J13. It is easy enough for me to fix with an autofill or a macro but someone else will be deleting the rows and the #REF! error then destroys the reference for all people sharing the file until I get back and make the fix. I'm also trying to avoid deploying macros to other users as much as possible. Also having the inverse of the problem in that my VBA references DO NOT adjust for changes to the sheet like formulas adjust. i.e. the references in a VBA instruction such as: Sheets("Sheet1").Range("G4").Value = Sheets("Sheet1").Range("E50") must be reset to E49 if row 45 is deleted, whereas formulas on the sheet automatically change their references to E49. Excel just is not smart enough to read my mind I guess. Any help greatly appreciated. ed |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference Source Deleted & VBA ref adjustments
You are absolutely correct when you say that Excel is not a mind reader.
There is no way around the deleteing rows issue returning #Ref. You might be able to get around it using the Indirect function with a bit of work but I would advise against overusing it as indirect is a volatile function and uses up a lot of calculation overhead. IMO you are best off to design spreadsheets where the end user does not ever want to delete a row where a formula is referencing a cell on that row... As for your second problem with the VBA Macros one thing that you can do is to use range names instead of cell addresses. The range names will move when cells are inserted or deleted. This way your code will be dynamic as it uses the named range to navigate... -- HTH... Jim Thomlinson "unexpected" wrote: Is there a way to set a formula so that if the row that is referenced is deleted the formula automatically adjusts to the row that takes its place. I tried an absolute reference but still get the #REF! error. Specifically the formula in cell B12 on sheet 2 refers to cell J13 on sheet 1. If I delete row 13 on sheet 1 I would like cell B12 to now refer to the cell that was formally J14 but has now moved up into J13. It is easy enough for me to fix with an autofill or a macro but someone else will be deleting the rows and the #REF! error then destroys the reference for all people sharing the file until I get back and make the fix. I'm also trying to avoid deploying macros to other users as much as possible. Also having the inverse of the problem in that my VBA references DO NOT adjust for changes to the sheet like formulas adjust. i.e. the references in a VBA instruction such as: Sheets("Sheet1").Range("G4").Value = Sheets("Sheet1").Range("E50") must be reset to E49 if row 45 is deleted, whereas formulas on the sheet automatically change their references to E49. Excel just is not smart enough to read my mind I guess. Any help greatly appreciated. ed |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference Source Deleted & VBA ref adjustments
Just what I suspected. I'll try the named range substitution. Thanks for
the feedback. ed "Jim Thomlinson" wrote: You are absolutely correct when you say that Excel is not a mind reader. There is no way around the deleteing rows issue returning #Ref. You might be able to get around it using the Indirect function with a bit of work but I would advise against overusing it as indirect is a volatile function and uses up a lot of calculation overhead. IMO you are best off to design spreadsheets where the end user does not ever want to delete a row where a formula is referencing a cell on that row... As for your second problem with the VBA Macros one thing that you can do is to use range names instead of cell addresses. The range names will move when cells are inserted or deleted. This way your code will be dynamic as it uses the named range to navigate... -- HTH... Jim Thomlinson "unexpected" wrote: Is there a way to set a formula so that if the row that is referenced is deleted the formula automatically adjusts to the row that takes its place. I tried an absolute reference but still get the #REF! error. Specifically the formula in cell B12 on sheet 2 refers to cell J13 on sheet 1. If I delete row 13 on sheet 1 I would like cell B12 to now refer to the cell that was formally J14 but has now moved up into J13. It is easy enough for me to fix with an autofill or a macro but someone else will be deleting the rows and the #REF! error then destroys the reference for all people sharing the file until I get back and make the fix. I'm also trying to avoid deploying macros to other users as much as possible. Also having the inverse of the problem in that my VBA references DO NOT adjust for changes to the sheet like formulas adjust. i.e. the references in a VBA instruction such as: Sheets("Sheet1").Range("G4").Value = Sheets("Sheet1").Range("E50") must be reset to E49 if row 45 is deleted, whereas formulas on the sheet automatically change their references to E49. Excel just is not smart enough to read my mind I guess. Any help greatly appreciated. ed |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference Source Deleted & VBA ref adjustments
Rather than Indirect I tried the offset function to solve the first problem,
which also ignores deleted rows. Not sure if it has the same problem with resource hogging though, but I'm using it elsewhere in the sheet so not an issue so far. The solution looked like: Original forumula in cell B12 was - = Sheet1!J13 Revised formula - = OFFSET(Sheet1!$J$1,12,0) Seems to work fine. ed "Jim Thomlinson" wrote: You are absolutely correct when you say that Excel is not a mind reader. There is no way around the deleteing rows issue returning #Ref. You might be able to get around it using the Indirect function with a bit of work but I would advise against overusing it as indirect is a volatile function and uses up a lot of calculation overhead. IMO you are best off to design spreadsheets where the end user does not ever want to delete a row where a formula is referencing a cell on that row... As for your second problem with the VBA Macros one thing that you can do is to use range names instead of cell addresses. The range names will move when cells are inserted or deleted. This way your code will be dynamic as it uses the named range to navigate... -- HTH... Jim Thomlinson "unexpected" wrote: Is there a way to set a formula so that if the row that is referenced is deleted the formula automatically adjusts to the row that takes its place. I tried an absolute reference but still get the #REF! error. Specifically the formula in cell B12 on sheet 2 refers to cell J13 on sheet 1. If I delete row 13 on sheet 1 I would like cell B12 to now refer to the cell that was formally J14 but has now moved up into J13. It is easy enough for me to fix with an autofill or a macro but someone else will be deleting the rows and the #REF! error then destroys the reference for all people sharing the file until I get back and make the fix. I'm also trying to avoid deploying macros to other users as much as possible. Also having the inverse of the problem in that my VBA references DO NOT adjust for changes to the sheet like formulas adjust. i.e. the references in a VBA instruction such as: Sheets("Sheet1").Range("G4").Value = Sheets("Sheet1").Range("E50") must be reset to E49 if row 45 is deleted, whereas formulas on the sheet automatically change their references to E49. Excel just is not smart enough to read my mind I guess. Any help greatly appreciated. ed |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference Source Deleted & VBA ref adjustments
Offset is also Volatile which means that it has a lot of overhead. Check out
this link for a full explanation... http://www.decisionmodels.com/calcsecretsi.htm -- HTH... Jim Thomlinson "unexpected" wrote: Rather than Indirect I tried the offset function to solve the first problem, which also ignores deleted rows. Not sure if it has the same problem with resource hogging though, but I'm using it elsewhere in the sheet so not an issue so far. The solution looked like: Original forumula in cell B12 was - = Sheet1!J13 Revised formula - = OFFSET(Sheet1!$J$1,12,0) Seems to work fine. ed "Jim Thomlinson" wrote: You are absolutely correct when you say that Excel is not a mind reader. There is no way around the deleteing rows issue returning #Ref. You might be able to get around it using the Indirect function with a bit of work but I would advise against overusing it as indirect is a volatile function and uses up a lot of calculation overhead. IMO you are best off to design spreadsheets where the end user does not ever want to delete a row where a formula is referencing a cell on that row... As for your second problem with the VBA Macros one thing that you can do is to use range names instead of cell addresses. The range names will move when cells are inserted or deleted. This way your code will be dynamic as it uses the named range to navigate... -- HTH... Jim Thomlinson "unexpected" wrote: Is there a way to set a formula so that if the row that is referenced is deleted the formula automatically adjusts to the row that takes its place. I tried an absolute reference but still get the #REF! error. Specifically the formula in cell B12 on sheet 2 refers to cell J13 on sheet 1. If I delete row 13 on sheet 1 I would like cell B12 to now refer to the cell that was formally J14 but has now moved up into J13. It is easy enough for me to fix with an autofill or a macro but someone else will be deleting the rows and the #REF! error then destroys the reference for all people sharing the file until I get back and make the fix. I'm also trying to avoid deploying macros to other users as much as possible. Also having the inverse of the problem in that my VBA references DO NOT adjust for changes to the sheet like formulas adjust. i.e. the references in a VBA instruction such as: Sheets("Sheet1").Range("G4").Value = Sheets("Sheet1").Range("E50") must be reset to E49 if row 45 is deleted, whereas formulas on the sheet automatically change their references to E49. Excel just is not smart enough to read my mind I guess. Any help greatly appreciated. ed |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference Source Deleted & VBA ref adjustments
Jim. Thanks for the link.
Guess it only makes sense that for the formula to adjust to the change of a deleted row it would have to be volatile. This overall project is small enough that the short delay should not be an issue. thanks again for sharing your knowledge. ed "Jim Thomlinson" wrote: Offset is also Volatile which means that it has a lot of overhead. Check out this link for a full explanation... http://www.decisionmodels.com/calcsecretsi.htm -- HTH... Jim Thomlinson "unexpected" wrote: Rather than Indirect I tried the offset function to solve the first problem, which also ignores deleted rows. Not sure if it has the same problem with resource hogging though, but I'm using it elsewhere in the sheet so not an issue so far. The solution looked like: Original forumula in cell B12 was - = Sheet1!J13 Revised formula - = OFFSET(Sheet1!$J$1,12,0) Seems to work fine. ed "Jim Thomlinson" wrote: You are absolutely correct when you say that Excel is not a mind reader. There is no way around the deleteing rows issue returning #Ref. You might be able to get around it using the Indirect function with a bit of work but I would advise against overusing it as indirect is a volatile function and uses up a lot of calculation overhead. IMO you are best off to design spreadsheets where the end user does not ever want to delete a row where a formula is referencing a cell on that row... As for your second problem with the VBA Macros one thing that you can do is to use range names instead of cell addresses. The range names will move when cells are inserted or deleted. This way your code will be dynamic as it uses the named range to navigate... -- HTH... Jim Thomlinson "unexpected" wrote: Is there a way to set a formula so that if the row that is referenced is deleted the formula automatically adjusts to the row that takes its place. I tried an absolute reference but still get the #REF! error. Specifically the formula in cell B12 on sheet 2 refers to cell J13 on sheet 1. If I delete row 13 on sheet 1 I would like cell B12 to now refer to the cell that was formally J14 but has now moved up into J13. It is easy enough for me to fix with an autofill or a macro but someone else will be deleting the rows and the #REF! error then destroys the reference for all people sharing the file until I get back and make the fix. I'm also trying to avoid deploying macros to other users as much as possible. Also having the inverse of the problem in that my VBA references DO NOT adjust for changes to the sheet like formulas adjust. i.e. the references in a VBA instruction such as: Sheets("Sheet1").Range("G4").Value = Sheets("Sheet1").Range("E50") must be reset to E49 if row 45 is deleted, whereas formulas on the sheet automatically change their references to E49. Excel just is not smart enough to read my mind I guess. Any help greatly appreciated. ed |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reference Source Deleted & VBA ref adjustments
If the row above J13 is never deleted, then formula in Sheet 2, B12 could be
"=OFFSET(Sheet1!J12,1,0)". Or reference any cell on Sheet 1 and adjust row & Col offset numbers. "unexpected" wrote: Is there a way to set a formula so that if the row that is referenced is deleted the formula automatically adjusts to the row that takes its place. I tried an absolute reference but still get the #REF! error. Specifically the formula in cell B12 on sheet 2 refers to cell J13 on sheet 1. If I delete row 13 on sheet 1 I would like cell B12 to now refer to the cell that was formally J14 but has now moved up into J13. It is easy enough for me to fix with an autofill or a macro but someone else will be deleting the rows and the #REF! error then destroys the reference for all people sharing the file until I get back and make the fix. I'm also trying to avoid deploying macros to other users as much as possible. Also having the inverse of the problem in that my VBA references DO NOT adjust for changes to the sheet like formulas adjust. i.e. the references in a VBA instruction such as: Sheets("Sheet1").Range("G4").Value = Sheets("Sheet1").Range("E50") must be reset to E49 if row 45 is deleted, whereas formulas on the sheet automatically change their references to E49. Excel just is not smart enough to read my mind I guess. Any help greatly appreciated. ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Auto update to "tab" in a reference link | Excel Worksheet Functions | |||
recovering chart source data from deleted files | Excel Discussion (Misc queries) | |||
Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel | |||
how do I format a cell reference to move as source changes | Excel Worksheet Functions |