Software

General discussion

Gravatar
0 Votes
Locked

Excel formulae - 'delinking' drag+drop?

By Mike66 ·
Question: Is it possible to stop an Excel formula from automatically amending itself when data in a 'precedent' cell is dragged to a new location. I want the formula to continue to act on the original cell - not move to the new cell where I have dragged the cell contents to. This problem also occurs with copy+paste or cut+paste.
My own solution: Using Copy+'Paste_Special/values?, this works! The formulae do not follow the moved data - But this solution is only OK for my own use - I can not rely on other users to never use drag+drop or copy+paste on my workbook. They only have to make the mistake once to destroy formulae and make the workbook un-usable. [So, Please do not offer this as a solution :-)]
Background:I use MSExcel as a text 'database', with a data entry worksheet of columns such as Department, Last Name, First Name, email address etc. (There are further columns which could, say, show make/model of car owned, City of residence, etc.)
I have about fifteen 'report' worksheets which show the data in different formats. An example might be a report that lists only people who live in a particular city, or who own a particular car.
My problem is that if I use drag+drop or copy+paste anywhere on the data-entry worksheet, the formulae on the 'report' worksheets 'watch' where I move the data to and 'follow' the data by amending themselves automatically to look at the new position of the moved data.
The SOLUTION WHICH I USE at present is to only use Copy+Paste_Special/values. This stops the formulae from 'following' the data - but this solution is OK only for my own use. If I allow other people to use the Workbook, they do not appreciate the problem and very quickly corrupt the formulae by using drag+drop or copy+paste on the data entry worksheet.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

by Don Christner In reply to Excel formulae - 'delinki ...

You can tell Excel to only look at an exact column, row or both by using the $. For example, if you want to lock in column A but not lock in the exact cell you would use $A1. If you want to lock in the exact cell you would use $A$1 in your formula. That way, if the data moves the formula doesn't follow.

Don C

gravatar
Collapse -

by Mike66 In reply to

I believe there is no answer to my problem except getting the Excel team at Microsoft to add a new Option switch to give the user the choice to de-link cell formulae from precedent cell references.
Thanks to Don Christner for his response which, although it wasn't the answer, at least got me thinking deeper about the problem.

gravatar
Collapse -

by Mike66 In reply to Excel formulae - 'delinki ...

Thanks for your suggestion Don - But it is not the solution. I use the $A$1 ?absolute? cell reference regularly and it is very useful for locking a specific ?precedent? cell/array reference (within a formula) while copying the formula down a column or across a row. However, it does not "lock-in" the precedent cell reference. Just now I double-checked on a blank worksheet.
Example: A1 "John", B1 "Smith", C1 ?=LEFT($A$1,1)&". "&$B$1?. C1 will display ?J. Smith?. If you now drag cells A1 and/or B1 to anywhere on the worksheet - the formula in C1 will ?follow? them and still display ?J. Smith?. The formula in C1 might now look like ?=LEFT($S$47,1)&?. ?&$E$2?
My own less than satisfactory solution is to select Tools menu/Options/Edit. then untick ?Allow cell drag-and-drop?. This prevents drag and drop throughout the workbook and? sort of? solves the problem. But not very elegantly. Nor does this prevent Cut+Paste from corrupting formulae in the same way. In the meantime I have posted a request on Microsoft?s wish-list website to add a global switch on the ?Options? dialog to de-link formulae from edits on or within a ?precedent? cell.
More suggestions would be welcome.

gravatar
Collapse -

by Mike66 In reply to Excel formulae - 'delinki ...

My main objective is still to de-link formulae from edits involving precedent cells.
But, following on from the idea of disabling drag+drop throughout the workbook [Tools/Options/Edit - untick 'allow drag and drop'], does anyone know how to disable Cut [Excel - Edit menu 'Cut' Ctrl+X]?
With drag+drop and 'Cut' disabled, it would be very much more difficult for other users to corrupt formulae in my workbook.
I should like to keep the question open for a bit longer (before distributing the promised points) and look forward to receiving some ideas.

gravatar
Collapse -

by Mike66 In reply to Excel formulae - 'delinki ...

This question was closed by the author

gravatar
Collapse -

by Mike66 In reply to Excel formulae - 'delinki ...

Excel formulae - 'delinking' drag+drop.
THE PROBLEM IS SOLVED!!
For anyone wishing to be able to drag+drop or cut+paste data into/out of a "data" cell without dependent formulae amending their cell references - use the Excel INDIRECT function.
The example shown in my previous comments will demonstrate.
If A1="John" and B1="Smith" and C1=LEFT(A1,1)&". "&B1, then C1 will display "J. Smith".
If you want the formula in C1 to continue to reference cells A1 and B1, regardless of what you do with the data (eg. drag+drop or cut+paste out of A1/B1) you do the following.
Let,say,U1=INDIRECT("A1") and V1=INDIRECT("B1") Set C1=LEFT(U1,1)&". "&V1.
Now, U1 and V1 will IGNORE anything you do with the data in A1 and B1 and continue to reference A1 and B1 for ever!
PROBLEM SOLVED!!!

Back to Software Forum

Start or search

Related Discussions

Related Forums