Wanted - help with generating INDIRECT("A1") from INDIRECT (A1)

  • Thread starter Thread starter Dan E
  • Start date Start date
D

Dan E

I have trouble with users occasionally cutting, moving or deleting cells
that are referenced by formulas, so I get #REF! errors that I then have to
clean up. Ken Wright suggested I replace direct references to the editable
data cells in the formulas (e.g. A1) with INDIRECT references using a text
string (e.g. INDIRECT("A1"). Sounds like a great idea, BUT don't know if
it's achievable, because there are many formulas and many cells, and, since
I HAVE to use a text string for this method to work, I can't see a way to
change the formula in one place, then copy and paste into other locations in
a way that would maintain the relative addressing of the text in the string.
If anyone knows of a trick to do this, I'd be grateful beyond measure!

TIA,

Dan
 
Dan E (removethis) wrote...
I have trouble with users occasionally cutting, moving or deleting cells
that are referenced by formulas, so I get #REF! errors that I then have to
clean up. Ken Wright suggested I replace direct references to the editable
data cells in the formulas (e.g. A1) with INDIRECT references using a text
string (e.g. INDIRECT("A1"). Sounds like a great idea, BUT don't know if
it's achievable, because there are many formulas and many cells, and, since
I HAVE to use a text string for this method to work, I can't see a way to
change the formula in one place, then copy and paste into other locations in
a way that would maintain the relative addressing of the text in the string.
If anyone knows of a trick to do this, I'd be grateful beyond measure!
Click to expand...

This is what R1C1-style addressing is for. If you want a formula
effectively the same as =B3*(1+X) in cell F5, and you want to ensure
it'll remain as-is no matter what happens to cell B3, row 3 or column
B, but you want to maintain relativity when filling or copying/pasting,
try

F5:
=INDIRECT("R[-2]C[-4]",0)*(1+X)

However, I'd just protect worksheets, which will prevent users from
inserting or deleting rows or columns, and I'd add the following line
of code to the Activate and SelectionChange event handlers for the
protected worksheets.

If Application.CutCopyMode = xlCut Then Application.CutCopyMode = False

This isn't absolutely foolproof because it's still possible to cut &
paste between workbooks and windows into the same workbook, so you'd
also need workbook-level Activate and WindowActivate event handlers to
include code like

If ActiveSheet.ProtectContents _
And Application.CutCopyMode = xlCut Then _
Application.CutCopyMode = False

still, this can be bypassed by sophisticated users.


Far more effective is error message formulas like

=IF(COUNTIF(BigRange,"#REF!"),"You've carelessly or intentionally
corrupted this workbook. Notification of this has been sent to Human
Resources, which will be included in your personel file. If you persist
in misusing company software, you could face discipinary action up to
and including termination.",
"")

I guarantee you this grabs users' attention.
 
Many many thanks, Harlan - lots for me to sift through and chew on - not
very familiar with VBA. Love the warning message at the end, however,
sometimes the user is my wife, so I think I'll give that a miss! :-)

Thanks again,

Dan
Harlan Grove said:
Dan E (removethis) wrote...
I have trouble with users occasionally cutting, moving or deleting cells
that are referenced by formulas, so I get #REF! errors that I then have to
clean up. Ken Wright suggested I replace direct references to the editable
data cells in the formulas (e.g. A1) with INDIRECT references using a text
string (e.g. INDIRECT("A1"). Sounds like a great idea, BUT don't know if
it's achievable, because there are many formulas and many cells, and, since
I HAVE to use a text string for this method to work, I can't see a way to
change the formula in one place, then copy and paste into other locations in
a way that would maintain the relative addressing of the text in the string.
If anyone knows of a trick to do this, I'd be grateful beyond measure!
Click to expand...

This is what R1C1-style addressing is for. If you want a formula
effectively the same as =B3*(1+X) in cell F5, and you want to ensure
it'll remain as-is no matter what happens to cell B3, row 3 or column
B, but you want to maintain relativity when filling or copying/pasting,
try

F5:
=INDIRECT("R[-2]C[-4]",0)*(1+X)

However, I'd just protect worksheets, which will prevent users from
inserting or deleting rows or columns, and I'd add the following line
of code to the Activate and SelectionChange event handlers for the
protected worksheets.

If Application.CutCopyMode = xlCut Then Application.CutCopyMode = False

This isn't absolutely foolproof because it's still possible to cut &
paste between workbooks and windows into the same workbook, so you'd
also need workbook-level Activate and WindowActivate event handlers to
include code like

If ActiveSheet.ProtectContents _
And Application.CutCopyMode = xlCut Then _
Application.CutCopyMode = False

still, this can be bypassed by sophisticated users.


Far more effective is error message formulas like

=IF(COUNTIF(BigRange,"#REF!"),"You've carelessly or intentionally
corrupted this workbook. Notification of this has been sent to Human
Resources, which will be included in your personel file. If you persist
in misusing company software, you could face discipinary action up to
and including termination.",
"")

I guarantee you this grabs users' attention.
Click to expand...
 
Back
Top