Way around CUT problems...

[DanF]

Afternoon all

I have a sheet, say Sheet 1, which has no formulas or macros; but is
referenced by a whole host of other sheets in the workbook which use the data
in Sheet 1 to provide the user with a load of useful stuff.

If (or when) the users use 'Edit > Cut' in Sheet 1 moving data around, the
formulas in the sheets referencing Sheet 1 try to upate their references and
I'm left with #REF erorrs everywhere.

Is there a way around the default 'Cut' functionality or a way to preserve
the formula references so that this doesn't happen?

Best regards,
DanF

 

[Pete_UK]

Tell your users to use <copy> and <paste> then go back to the original
cell(s) and press the <delete> key - your formulas will remain intact
(though not necessarily looking at the correct cells).

Hope this helps.

Pete

 

[Kevin B]

If you assign names to the ranges you're referencing then the named ranges
move with the data when cut. Formulas referencing the name of the range
automatically adjust to the named range's cell coordinates.

From the menu INSERT/NAME/DEFINE

 

[Jim Rech]

They only way you can prevent Cut/Pastes and Drag & Drops, which have the
same effect, is with code. With Excel 2007 you would also have to include
RibbonX in the file that disables these things.

Sub CutsOff()
AllowCuts False
End Sub

Sub CutsOn()
AllowCuts True
End Sub

Sub AllowCuts(bEnable As Boolean)
Dim oCtls As CommandBarControls, oCtl As CommandBarControl
Set oCtls = CommandBars.FindControls(ID:=21) ''Cut
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = bEnable
Next
End If
''Disable Tools, Options so D&D cannot be restored
Set oCtls = CommandBars.FindControls(ID:=522)
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = bEnable
Next
End If
With Application
.CellDragAndDrop = bEnable
If bEnable Then
.OnKey "^x"
.OnKey "+{Del}"
Else
.OnKey "^x", ""
.OnKey "+{Del}", ""
End If
End With
End Sub


--
Jim
| Afternoon all
|
| I have a sheet, say Sheet 1, which has no formulas or macros; but is
| referenced by a whole host of other sheets in the workbook which use the
data
| in Sheet 1 to provide the user with a load of useful stuff.
|
| If (or when) the users use 'Edit > Cut' in Sheet 1 moving data around, the
| formulas in the sheets referencing Sheet 1 try to upate their references
and
| I'm left with #REF erorrs everywhere.
|
| Is there a way around the default 'Cut' functionality or a way to preserve
| the formula references so that this doesn't happen?
|
| Best regards,
| DanF

 

[DanF]

Hi Pete

Yeah, I've done that, but you just know that when you tell someone not to
use such an engrained functionality as CUT, that it's going to happen at some
point in the future!

 

[DanF]

So, if I'm understamding you right - the formulas adjust?

What if I don't want the formulas to adjust?
What if just wanted the formulas on the other sheets to stay pointing at the
same place when I created them and not to adjust?

Is that possible, or am I a numpty?

 

[DanF]

Jesus Jim, thanks for that - your either a demon coder or have done this before

Thanks for that little nugget

;-)

 

[Ken Johnson]

Afternoon all

I have a sheet, say Sheet 1, which has no formulas or macros; but is
referenced by a whole host of other sheets in the workbook which use the data
in Sheet 1 to provide the user with a load of useful stuff.

If (or when) the users use 'Edit > Cut' in Sheet 1 moving data around, the
formulas in the sheets referencing Sheet 1 try to upate their references and
I'm left with #REF erorrs everywhere.

Is there a way around the default 'Cut' functionality or a way to preserve
the formula references so that this doesn't happen?

Best regards,
DanF

The INDIRECT function can be used to prevent a formula from changing.
Example...
=SUM(Sheet1!A1:A10) will change to =SUM(Sheet1!B1:B10) after Sheet1!
A1:A10 is cut then pasted into Sheet1!B1:B10, while
=SUM(INDIRECT("Sheet1!A1:A10")) would not be affected.

Ken Johnson

 

[DanF]

Hi Ken

Thanks for that mate, I'll give it a try.
Although I have god only knows how many formulas to update!!!

Oh well, live and learn.....

Thanks again
------

 

[Jim Rech]

The INDIRECT function can be used to prevent a formula from changing

Yes it can Ken but consider this. Say Sheet1 has the data and Sheet2 the
formulas that reference sheet1. If you write the formulas via indirects and
the user drags the sheet1 data around, the sheet2 formulas will still
reference the original cells...but the data will no longer be there.

Not to mention maintenance issues. Say you want to rearrange sheet1.
Insert a row or two for new data items to go in their logical place. The
indirects will not adjust. Ugly.

--
Jim
| > Afternoon all
| >
| > I have a sheet, say Sheet 1, which has no formulas or macros; but is
| > referenced by a whole host of other sheets in the workbook which use the
data
| > in Sheet 1 to provide the user with a load of useful stuff.
| >
| > If (or when) the users use 'Edit > Cut' in Sheet 1 moving data around,
the
| > formulas in the sheets referencing Sheet 1 try to upate their references
and
| > I'm left with #REF erorrs everywhere.
| >
| > Is there a way around the default 'Cut' functionality or a way to
preserve
| > the formula references so that this doesn't happen?
| >
| > Best regards,
| > DanF
|
| The INDIRECT function can be used to prevent a formula from changing.
| Example...
| =SUM(Sheet1!A1:A10) will change to =SUM(Sheet1!B1:B10) after Sheet1!
| A1:A10 is cut then pasted into Sheet1!B1:B10, while
| =SUM(INDIRECT("Sheet1!A1:A10")) would not be affected.
|
| Ken Johnson