Reference Cells into Merged Cells

**Family Man** · 11-06-2013, 12:06 PM

Hello!

I have a spreadsheet list that has no merged cells. I need to copy that spreadsheet into another spreadsheet where each row is actually 2 merged rows. I thought that I could manually reference the first few rows, then drag them down to copy and hope that excel would be smart enough to pick up the pattern. But it doesn't, and keeps copying over every other row. Is there a way to quickly copy a spreadsheet with individual rows into a spreadsheet with merged (two rows) cells? Seems like it should be doable.

Thanks.

**Family Man** · 11-06-2013, 02:55 PM

I have attached an example file of what I am trying to do. Thanks for your time.

**Family Man** · 11-07-2013, 02:21 PM

I have been doing some research, and I should be able to solve this problem with the indirect function. But it isn't working. What I did was create another row that is numbered to match the rows in the second spreadsheet. I should be able to use the indirect function to use that number in the formula reference. I have attached what I am talking about, as well as my try at the correct formula. Can someone please help me out? Here is the formula I tried:

="Sheet2!A"&INDIRECT(A1)

Thanks.

**popipipo** · 11-07-2013, 02:42 PM

=INDIRECT("Sheet2!A"&A1)

Try this formula

**FDibbins** · 11-07-2013, 02:45 PM

Try this, copied down...
=INDIRECT("'sheet2'!A"&ROUNDUP(ROW()/2,0))

**newdoverman** · 11-07-2013, 03:57 PM

Why not uncomplicated sheet2 by removing the merging of the cells and double the row height instead and use VERTICAL CENTERING. Then you can use a simple formula like =SHEET1!A1 and fill across and down.