#1
|
|||
|
|||
Drag and Drop into an input range results in #REF! error
Hi all,
I have worksheet with an input range of, for example, N2:O2. There are a number of formulas in adjacent cells that reference N2 and O2. The formulas work fine when the entries are input via Ctrl+V or manually typed in. To speed things up, since the input data is another area in the same worksheet, I'd like to enable the users to simply drag and drop into N2 and O2, however, the formulas error out immediately with #REF! since perhaps the relative references appear to have been impacted. I've tried changing the formulas to absolutes but that had no positive impact. Any ideas? Many thanks! |
#2
|
||||
|
||||
Any ideas why the formula returns an error? I guess by drag and drop (cut and paste), the N reference is deleted. Unlike in drag and fill (copy and paste) where the values are copied to the N cells.
|
#3
|
|||
|
|||
$N$2 - the reference remains always to $N$2 (e.g. you copy formula from cell X2 to Y3);
N2 - both column and row references change (e.g. you copy from X2 to Y3 and you get the reference to M3); $N2 - only row reference does change (e.g. you copy from X2 to Y3 and you get the reference to $N3); N$2 - only column reference does change (e.g. you copy from X2 to Y3 and you get the reference to M$2). What you mean by Drag and Drop? To copy formulas: a) You copy formula, and paste it to any range - references are changed according to rules above; b) You hover mouse cursor over right lower edge of cell until a cross appears, and then click on this corner and then drag cursor up/down/left/right keeping left mouse button down - references are changed according to rules above; c) You activate edit mode for cell with formula, copy the formula from formula bar, press Esc to leave formula bar, activate edit mode for target cell, paste the formula into formula bar, and press Enter - references remain same as in source cell. From your post, I get feeling the formula in target cell(s) refers to wrong cell. This may be easily happen, when you copy formulas to range up/left of source cell. E.g. when the formula in cell y10 refers to cell A1, and you copy it into cell in column Y or row 9, then it will refer to non-existing cell. |
#4
|
|||
|
|||
Appreciate the responses!
I was attempting to help speed up data entry for my users by allowing them to select a small range of cells in an unstructured area and simply dragging them over to input fields where the formulas do their thing (data validation). Its probably almost just as quick to do a Ctrl+C and Ctrl+V but since they will be doing this multiple times, thought it would be nice to allow simple drag and drop. In researching this, appears to be an Excel limitation. |
#5
|
||||
|
||||
Use Indirect, eg.:
=INDIRECT("B5") and to avoid loads of formulae with Indirect in, you could put formulae such as the above in only a few cells and have the more complex formulae refer to those cells insteasd of the ones being drag'n'dropped to |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Drag & Drop of pictures | Brigitte11 | OneNote | 0 | 10-25-2017 09:29 AM |
automating drag & drop | rob171171 | Excel | 9 | 04-23-2016 07:09 AM |
Drag and drop macro | Amanoo | PowerPoint | 2 | 06-15-2012 04:01 AM |
Drop and drag macro | Jamtart | PowerPoint | 3 | 04-15-2012 11:05 PM |
Error Copying file or folder - Drag-N-Drop--> Network Shared | Kris.Fulgham | Outlook | 1 | 10-17-2005 09:02 AM |