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

Dragging/Copying formula's to Merged Cells

dvm49

dvm49

Member
Hello All,

I have 2 sheets, Sheet1 containing Country Names and Sheet2 has some data's in it. In Sheet2, I have merged B&C, D&E, F&G and so on and in this merged cells now I wanted to apply the formula to get the country name. I know copy/paste/dragging formula doesn't work inside the merged cells. What is the alternate way to achieve this?

I have attached a sample sheet with this message. Any help would be appreciated.
 

Attachments

  • Drag_Formulas_To_Merged_Cells.xlsx
    12.3 KB · Views: 42
dvm49
Could You run next to get Your formulas?
Code: 
Sub Do_It()
    y = 2
    Do
        Sheets("Sheet2").Cells(1, 2 + (y - 2) * 2).Formula = "=sheet1!A" & y
        y = y + 1
    Loop Until Sheets("Sheet1").Cells(y, 1) = Empty
End Sub
 
Merged cells are one of MS worst inventions when they need to be referenced
Remove the merging and use the " Center across selection" format instead
Then in B1 try something like =IF(MOD(COLUMN(),2)=0,INDEX(Sheet1!$B$12:$B$100,COLUMN()/2),"") and drag right

Adapt range as needed
 
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.
 
You must log in or register to reply here.
Back
Top