Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Drag and Drop

Drag and Drop

Drag and Drop

I want to be able to use "drag and drop" editing to move data in a spreadsheet, but I would like the formulas that access the data to pick the data from the same place.

The application is a project tooling budget by part and by month.  When the schedule moves, I would like to simply drag the expenditures to different months and let them go.  What happens is the formulas that summarize the data by year get changed so they aren't looking at the right months any more.  I was able to get around it by putting the detail data in its own file, and summarizing in a separate file.  I can drag and drop on the detail file while the summary file is closed, and when I reopen the summary file it finds the correct data.

Is there any way of doing this without using two files?

RE: Drag and Drop

Yes, but you can't drag and drop. If you select the data you want to move, copy it to the clipboard and then paste it into the new location then the formula referring to the new cells will be unchanged. It sounds a bit too obvious - is that what you meant?


Greg Locock

RE: Drag and Drop

For what you want to do why not have the data in a separate worksheet from the summary, or on different portion of the same worksheet. You could create a summary sheet and data sheet in the same workbook and simply add data to the end of a "live data sheet" as required. The summary could select the data by the use of a drop down list box to select the required month/year and hence data for that period.

You do not need to move data then as it can be selected by the use of lookup functions related to the month/year selected. To add data you would simnply move to the end of the data sheet and add your data.

This can however become a quite complex spreadsheet and would need a reasobale level of Excel knowledge to create it. You might like to try some of the shareware or other software that is available for inventory work.


RE: Drag and Drop

I hope I understand your question correctly.  If so, have you attempted writing your formulas using the "$" symbol to "lock" the column and/or row reference.  If not, it is as simple as this:

To lock the column reference only you would rewrite A1 as $A1.
Now you can drag a formula with this type reference and only update the row number dynamically.

To lock the row reference only, you would rewrite it as A$1.
This formula will only dynamically update the column reference when dragged about.

To lock both, you would rewrite it as $A$1.
Now you can drag it all about and always keep the reference pointing to A1.

RE: Drag and Drop

Takoga is correct!  The $ will lock the reference row/column/both.  Try that in all of your formulas can move.

Good Luck!

RE: Drag and Drop

Doesn't work. The original poster wants to drop data into destination cells

cell a1= 4

cell a2= =a1

cell b1=  5

Now drag and drop the 5 into the cell a1 that did contain the 4. Cell a2 now says =#REF

If you do it the way I suggested it does work.


Greg Locock

RE: Drag and Drop

cell a2= =$a$1

RE: Drag and Drop

You're right!  I will see if I can figure it out.

RE: Drag and Drop

If you are not trying to copy a formula, you can make GregLocock's example work by clicking on cell B1, then pointing at the border of cell B1, holding down the RIGHT mouse button, dragging over to cell A1, letting go and then selecting "Copy Here".  This will avoid the #REF showing up in formaulas that reference it.  If cell B1 contains a formula and you want to drag and drop you can use the same procedure but select "Copy Here as Values Only".  If you want the entire formula in B1 to move, you will need to make sure that all the references are absolute (have the $ signs as described above) and then select "Copy Here"

I think that's what you were trying to do...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close