Drag and Drop 2

[tcampbe1]

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?

 

[GregLocock]

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? Cheers

Greg Locock

 

[sc]

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.


regards
sc

 

[Takoga]

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.

 

[melone]

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

Good Luck!

 

[GregLocock]

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

eg
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.
Cheers

Greg Locock

 

[melone]

cell a2= =$a$1

 

[melone]

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

 

[reichertc]

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...