Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-19-2020, 10:34 AM
DJ0691 DJ0691 is offline Drag and Drop into an input range results in #REF! error Windows 10 Drag and Drop into an input range results in #REF! error Office 2013
Novice
Drag and Drop into an input range results in #REF! error
 
Join Date: Jan 2020
Posts: 18
DJ0691 is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 02-19-2020, 03:05 PM
Marcia's Avatar
Marcia Marcia is offline Drag and Drop into an input range results in #REF! error Windows 7 32bit Drag and Drop into an input range results in #REF! error Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 257
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #3  
Old 02-20-2020, 05:52 AM
ArviLaanemets ArviLaanemets is offline Drag and Drop into an input range results in #REF! error Windows 8 Drag and Drop into an input range results in #REF! error Office 2016
Expert
 
Join Date: May 2017
Posts: 545
ArviLaanemets is a jewel in the roughArviLaanemets is a jewel in the roughArviLaanemets is a jewel in the rough
Default

$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.
Reply With Quote
  #4  
Old 02-20-2020, 06:07 AM
DJ0691 DJ0691 is offline Drag and Drop into an input range results in #REF! error Windows 10 Drag and Drop into an input range results in #REF! error Office 2013
Novice
Drag and Drop into an input range results in #REF! error
 
Join Date: Jan 2020
Posts: 18
DJ0691 is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 02-20-2020, 10:16 AM
p45cal p45cal is offline Drag and Drop into an input range results in #REF! error Windows 10 Drag and Drop into an input range results in #REF! error Office 2019
Expert
 
Join Date: Apr 2014
Posts: 364
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Drag & Drop of pictures Brigitte11 OneNote 0 10-25-2017 09:29 AM
Drag and Drop into an input range results in #REF! error automating drag & drop rob171171 Excel 9 04-23-2016 07:09 AM
Drag and Drop into an input range results in #REF! error 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


All times are GMT -7. The time now is 12:10 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft