Drag and Drop editing that does not change cell references

[Guest]

I have a need to drag a cell value from one part of a spreadsheet to another
part of the spreadsheet in such a way that when the values replace the old
values at their new location I do not get a #REF error.

What I'm trying to accomplish is this: I have a list of data that needs to
be arranged manually into a second list in order to assemble a work schedule.
I want to drag the data from the original data inventory list into the newly
arranged schedule where calculations will be performed in real time as new
data is added to the new list. (I need to see a running total.) By dragging
the data away from the original data inventory I can assure that I do not
have duplicates in the new schedule as each data line would be eliminated
from the inventory as it is added to the new schedule.

Right now this can be done by COPYING the data line from the data inventory
and PASTING it to it's new location and then returning to the original data
inventory and DELETING the now copied line. However, this is cumbersome
considering the amount of data that has to be moved and re-arranged even
within the new location. Plus implementing a drag and drop process makes it
super easy to use for anyone.

 

[Tim Williams]

You could try using INDIRECT() in any formulas which point to the drop
target range.

Tim

 

[Guest]

Dave it sounds like you might have formulas in the cells you are trying to
drag and drop. When you try to move those by dragging, it changes the cell
references that they were calculating and produces the #Ref error flag.

 

[Guest]

Yes, there are formulas there and they are necessary. That's why I need the
work around.

Dave

 

[Guest]

I am not sure exactly what you are trying, but here is a way to drag
a cell and then select what you want to drop in the new location.
1. Make sure drag & drop is turned on.
2. Select the cell to drag and wait for the drag icon to appear.
3. When the icon appears, use the right mouse button to drag the cell.
4. When the drop location is reached, release the mouse button.
5. Select the drop option from the pop up menue that appears.

I found that I could get the paste values effect by using this procedure.
That did not produce a #Ref error flag in a precedent or dependent cell.
Good Luck.

 

[Dave T]

Tim,

INDIRECT() is what I needed. Thanks,it works like a dream!

Dave