• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Swapping values of 2 cells by drag and drop

Ohh half of my message is gone.. Re posting..:

Yes I read this before but it is not working with merged cells.
Actually Drag and Drop is not necessary. Swapping is what I need.
 
You are trying to work with merged cells, a big no no, you will have to un-merge all cells in the data area you are trying to work with.

The merged cells with the 3000 value are a total of 11 cells, the merged cells with the 2000 vale are a total of 11 cells, but youcannot swap data around with copy/paste in such ridiculous merged cells.

Why would anyone want to merge cells in this way?????


Avoid merging cells


Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.


For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.


In addition, not all cell formats, stick once you emerge a cell.


You can't sort a column with merged cells.


You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.


You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!


Merging cells in columns and rows could lead to data loss, bad thing.


Formulas and Functions that refer to merged cells will not work, bad thing.


Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.


Center Across Selection is a far better alternative to merging.


To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.


You will get the desired look you want but without the merged cell's problems.



.
 
Nice tip.. But again drag and drop is not working. It moves the first cell through the second cell but second cell is not replacing to first cell's place.

What if we try macro buttons? Can we use a macro button to swap cells? Is that possible?
 
I can not. That excel file is a part of huge excel file which all merged cells are connected to each other and writing datas will be very difficult if I unmerged them. :)
 
Hello I found a way and I want to ask a new question to learn..

I found a macro which swap values between 2 merged cells.

Can I make this button outside of the excel sheet at the top options, like cut, copy etc?

Please see the below file.

Thank you..
 

Attachments

  • Sheet 2.xlsm
    26.6 KB · Views: 2
That excel file is a part of huge excel file which all merged cells are connected to each other and writing datas will be very difficult if I unmerged them.

Then you are always going to have trouble with this work book, if you have so many merged cells then trouble big style, did you read the limitations in post #8.

In the sample you posted, 11 cells have been merged together to fit one word!
This means that all columns have a cell width of 1.29, making them just about useless for any data input.



.



.
 
Sir, these merged cells are not useless. All of them shows the pallets size.

All collours refers different size of pallets so we can understand which pallet starts where to where. So merged cells are important and unamendable.

Please see the below file..

Thanks..
 

Attachments

  • Sheet (2).xlsm
    44.1 KB · Views: 3
Yes the eyes see more when more is uploaded, we were working on the sample you posted with limited or no explanation.
 
I was working on a different page and now working on my original file. The problem is, I must put swap buttons into every palet positions otherwise I should every time go to swap button. Swap button is not a moving button due to on the sheet and it makes me a bit tedious every time. This sheet will be larger and will have more pallet sizes so swap button will be sometimes far away from the palet position whıch I want to swap, so I need to copy same button to all of the positions. Can I make a only 1 moving swap button or 1 button into excel toolbar? If I can do then it will solve my works in a short time.
 
Last edited:
Back
Top