Drag and Drop editing that does not change cell references

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave T - 21 Nov 2007 23:39 GMT

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.

Reply to this Message

Tim Williams - 22 Nov 2007 01:33 GMT

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

Tim

>I have a need to drag a cell value from one part of a spreadsheet to
>another
[quoted text clipped - 22 lines]
> it
> super easy to use for anyone.

Reply to this Message

Dave T - 24 Nov 2007 18:27 GMT

Tim,

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

Dave

> You could try using INDIRECT() in any formulas which point to the drop
> target range.
[quoted text clipped - 27 lines]
> > it
> > super easy to use for anyone.

Reply to this Message

JLGWhiz - 22 Nov 2007 01:42 GMT

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.

> 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
[quoted text clipped - 15 lines]
> within the new location. Plus implementing a drag and drop process makes it
> super easy to use for anyone.

Reply to this Message

Dave T - 22 Nov 2007 03:28 GMT

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

Dave

> 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
[quoted text clipped - 19 lines]
> > within the new location. Plus implementing a drag and drop process makes it
> > super easy to use for anyone.

Reply to this Message

JLGWhiz - 22 Nov 2007 04:33 GMT

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.

> Yes, there are formulas there and they are necessary. That's why I need the
> work around.
[quoted text clipped - 24 lines]
> > > within the new location. Plus implementing a drag and drop process makes it
> > > super easy to use for anyone.

Reply to this Message