General discussion
-
Topic
-
Excel formulae – ‘delinking’ drag+drop?
LockedQuestion: 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.