Reference Source Deleted & VBA ref adjustments

[Guest]

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

 

[Guest]

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...