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

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

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

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

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

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

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