Make Excel's "grab and drag" behave like cut and paste "values only" ?

[tur13o]

Excel question.



Is there an easy way to make Excel's "grab and drag" behave like cut and
paste "values only" ?



Scenario :



One sheet has input data

Another sheet has formula to display output data which uses the input data
as its variables

There is a close positional match of data on both sheets (e.g. data entered
A1 on sheet 1 affects the results in A1 on sheet 2)



What I would like to be able to use Excel's "grab and drag" (this is where
you put the mouse cursor right on the edge of a highlighted block of data
and drag it to another location using the mouse) capability to move chunks
of data on the input sheet. But not have the cell references on the output
sheet automatically changed to point to the new location of the data. i.e.
A1 on the output sheet is always dependant on data in A1 on the input sheet.



However I DO want excel automatically change the cell references when I
modify the structure of a sheet e.g. adding columns. So using OFFSET( )
does not work.



I have tried putting input and output sheets in separate excel files and
this kind of works. If you have both files open then Excel will synchronise
references if you modify the sheet layout. However it wont if you only have
the input sheet open. So with only the input sheet open you can grab and
drag all you like; when you open the output sheet it will not have it's
references changed J. But again this is not ideal since you have to keep
closing and opening the output sheet to see the results of your moving data.

 

[Jon Peltier]

I'm not sure if this is what you mean, but if you select your region,
then right click and drag it, you get a list of options (copy here, copy
here as values only, copy here as formats only, etc.). Experiment with
right-dragging; I use it all the time and it saves a ton of time.

- Jon

 

[Ture Magnusson]

Hi!

1. Select a range
2. Point at the border of the range (not a corner)
3. Hold down the RIGHT mouse button and drag to a new place
4. When releasing the mouse button, select "Copy here as values only"

 

[RagDyer]

I don't think that you really have a problem.
If you simply *copy* the range of cells in the "input" sheet instead of
*moving* them, and then paste them to your (their) new location, the
formulas on the "output" sheet remain unchanged.

Then just re-select the original range on the "input" sheet that you copied
and right click and choose "clear contents".
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Excel question.



Is there an easy way to make Excel's "grab and drag" behave like cut and
paste "values only" ?



Scenario :



One sheet has input data

Another sheet has formula to display output data which uses the input data
as its variables

There is a close positional match of data on both sheets (e.g. data entered
A1 on sheet 1 affects the results in A1 on sheet 2)



What I would like to be able to use Excel's "grab and drag" (this is where
you put the mouse cursor right on the edge of a highlighted block of data
and drag it to another location using the mouse) capability to move chunks
of data on the input sheet. But not have the cell references on the output
sheet automatically changed to point to the new location of the data. i.e.
A1 on the output sheet is always dependant on data in A1 on the input sheet.



However I DO want excel automatically change the cell references when I
modify the structure of a sheet e.g. adding columns. So using OFFSET( )
does not work.



I have tried putting input and output sheets in separate excel files and
this kind of works. If you have both files open then Excel will synchronise
references if you modify the sheet layout. However it wont if you only have
the input sheet open. So with only the input sheet open you can grab and
drag all you like; when you open the output sheet it will not have it's
references changed J. But again this is not ideal since you have to keep
closing and opening the output sheet to see the results of your moving data.

 

[tur13o]

The -right click - mouse drag -opting for values only -is the closest - but
you do have to re-select the initial range

being copied exactly and delete the contents. It's ok if you make no
mistakes.



But after the copy, the destination range is highlighted - there does not
seem to be a way to automatically select the source range of the copy
without undoing the copy in the process.



Could we have an enhancement to the way the grab and drag works - e.g.
holding down a modifier key (Alt or something) makes it behave like cut and
paste values only ? OR on the menu after a right mouse key grab and drag
have an option to move values / formulas only.

 

[Paul]

Unless it has been added to a more recent version than I use (which I
doubt), there is no cut/paste values on any menu. There is copy/paste
values. But if you cut a cell it is assumed that you want to paste it
somewhere else, in its entirety.

And please note that it's no good requesting extra features here. This is a
peer-to-peer newsgroup. People can ask and answer questions - it's about how
to use what's there in Excel. Complaining and requesting are pointless -
just shouting into thin air.