Formula from a single cell to a merged cell

[Mitch.Birlea]

Hi All,

I have a sheet with raw data(single cells) and a sheet with merged cells.

I am trying to link the same data from the single cell, to the merged cells, but when I drag down to copy the formula I find the formula skips rows (in this case it skips 7 rows). How would i overcome this so it only selects the data from that cell?

I hope this makes sense.

[Pete_UK]

It would help if you attached a sample Excel workbook, so we can see how your cells have been merged (I take it they are merged 7 deep).

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

[AliGW]

Very simply: don't use merged cells.

[Mitch.Birlea]

Thanks for your help Pete.

Please see attached example. In column D, The first cell works (looking at A2) but I want the next merged cell down to look at A3, and A4 and so on, but it skips to A9 and A16 and so on.

[AliGW]

As you are at the design stage, I would advise moving away from merged cells altogether - you are storing up pain and aggro for yourself as this project develops.

[Special-K]

in D2
=IF(MOD(ROW()-2,7)=0,INDEX(A$2:A$1000,INT((ROW()-2)/7)+1,1),"")
and copy down the column

Now merge the cells

[Mitch.Birlea]

Thank you very much Special K! It works!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Pete_UK]

Alternatively, with the cells already merged, put this in the first merged block (starting with D2):

=INDEX(A:A,INT((ROWS($1:1)-1)/7)+2)

Then CTRL-C and move the cursor to the next block (starting with D9) and CTRL-V, then move to D16 and CTRL-V again, and so on. Press Esc when you have finished.

(Some people are very nervous about merged cells !!)

Hope this helps.

Pete