How to apply formula on merged cells in Excel

behedwinNew memberJoinedMay 4, 2016Messages3Reaction score0Points0May 4, 2016#1HiIn sheet1 i have a list of data that i copy into my excel sheet.This date is the base of all things

How to apply formula on merged cells in Excel
behedwin

behedwin

New memberJoinedMay 4, 2016Messages3Reaction score0Points0
  • May 4, 2016
  • #1Hi

    In sheet1 i have a list of data that i copy into my excel sheet.
    This date is the base of all things i want to do...

    In sheet2 i have created a list of how i want to present the data.
    So i import the data i want from sheet1 to sheet2 with this formula :
    =Sheet1!A3
    etc.

    But since in sheet2 i have several merged cells.
    I can not copy the formula down to next row. It jumps several rows because of the merging of cells to create a nice presentation.

    Is there a way to tell excel to go one step anyway?
    For example: when i copy cell A3, i want the formula =sheet1!a3 to become =sheet1!a4
    Now because of the merged cells it jumps several cells down at once and i miss data.

    Is there a way to fix this?
Pecoflyer

Pecoflyer

Admin AlumnusJoinedOct 13, 2011Messages1,771Reaction score0Points36LocationBrussels BelgiumExcel Version(s)2010 on Xubuntu - O365
  • May 4, 2016
  • #2Hi

    merged cells are the worst thing MS invented (among many others, that's why I use Linux

)

Get rid of them and eventually color grid lines white, or some other formatting

behedwin

behedwin

New memberJoinedMay 4, 2016Messages3Reaction score0Points0
  • May 4, 2016
  • #3Ok, thanks

    I can get rid of the merged cells. But how do i copy a row so the formula skips 7 rows and then copy itself and then skips snother 7 rows.
Pecoflyer

Pecoflyer

Admin AlumnusJoinedOct 13, 2011Messages1,771Reaction score0Points36LocationBrussels BelgiumExcel Version(s)2010 on Xubuntu - O365
  • May 4, 2016
  • #4So there are 7 empty cells between each formula?

    Supposing you start in row1 then enter =IF(MOD(ROW(),8)<>1,"",INDIRECT("sheet1!!a"&INT(ROW()/8)+1)) in A1 and pull down as needed


    Adapt as required for your sheet


    INDIRECT being volatile, it might slow down your sheet. If necessary, come back so we can propose an alternate solution with INDEX/MATCH combination
behedwin

behedwin

New memberJoinedMay 4, 2016Messages3Reaction score0Points0
  • May 4, 2016
  • #5Hm im sorry, but i dont get it.
    Cant figure out how to use the indirect formula in my sheet.

    I want to copy the data from sheet1 to sheet2
    but in sheet2 there should be 7 empty cells/rows between each line of data that comes from sheet1

    so in sheet2 it should be
    A1 = Data from cell A1 in sheet1
    A2 = empty
    a3 = empty
    a4 = empty
    a5 = empty
    a6 = empty
    a7 = Data from cell A2 in sheet1
Pecoflyer

Pecoflyer

Admin AlumnusJoinedOct 13, 2011Messages1,771Reaction score0Points36LocationBrussels BelgiumExcel Version(s)2010 on Xubuntu - O365
  • May 4, 2016
  • #6In cell A1 of sheet2 enter =IF(MOD(ROW(),6)<>1,"",INDIRECT("sheet1!!a"&INT(ROW()/6)+1)) ( in your example you skipped 5 cells, not 7 is that it?)

    then just drag downN
New memberJoinedAug 27, 2013Messages901Reaction score0Points0Excel Version(s)Excel 2013
  • May 4, 2016
  • #7
behedwin said:But since in sheet2 i have several merged cells.
I can not copy the formula down to next row. It jumps several rows because of the merging of cells to create a nice presentation.
Is there a way to tell excel to go one step anyway?Click to expand...You can try
-Select multiple cells
-Click right mouse on the selection
-Format Cells
-Select tab Alignment
-In Horizontal field from drop down menu choose 'Center Across Selection'
-Data is now centered in multiple cells (alias merged)

Video liên quan