Referance other cells in merged cells without skipping data

[ldsands]

Hello all I've really found a lot of useful info these past few weeks as I've been working on a project for work but I haven't found something covering this problem I have. I know merged cells are, well, difficult but they are all over the templates I'm modifying and to not make the transition so jarring I'm home to keep the aesthetics of the old templates which means trying to keep the merged cells.

My issue regards the dragging of formulas in excel. When you drag a formula down to more cells the referenced cells will go down one at a time (unless told to do otherwise through the offset function for example). I want this to happen but I have merged cells that are bigger and used as labels for different parts of the project. So as I drag down the formula it will skip data in accordance with the corresponding cell to the top of the merged cell (I hope that makes sense). How do I make it so that as I drag down merged cells that no referenced cells are skipped?

A little about the situation I'm trying to keep this project as simple as possible due to the fact that I'm an undergrad in a position that will be changing every couple of years and I and my bosses want any other undergrad with basic understanding of Excel (and some research) will be able to deal with these templates I'm modifying in the future (in other words no macros and VB).

Thanks!

Excel question.jpg

[jhren]

There's several ways to do it. The following is just one:

In merged cells B2:B4 (essentially B2)...
=INDEX(A:A,(ROW()+1)/3)
...then drag down.

[ldsands]

There's several ways to do it. The following is just one:

In merged cells B2:B4 (essentially B2)...
=INDEX(A:A,(ROW()+1)/3)
...then drag down.

Well that didn't work but I was able to find something else and now my problem is a little bit different It wants to repeat the first one once then it will continue. Does anyone have any ideas? Also to be completely honest I've never used these formulas and so I don't understand them I'll do some research into them but for now I've got almost no clue what I'm doing here. Thanks for helping again.

Excel question.jpg

[jhren]

You eliminated row 1 in your second picture compared to first. That is why you get the repeat.

For the second picture....
=INDEX(A:A,(ROW()+2/3)

If you want a less volatile formula...
=INDEX(A:A,(ROW(A1)+2/3)

[ldsands]

You eliminated row 1 in your second picture compared to first. That is why you get the repeat.

For the second picture....
=INDEX(A:A,(ROW()+2/3)

If you want a less volatile formula...
=INDEX(A:A,(ROW(A1)+2/3)

It still doesn't work here is a screen of the results of your formula (in the middle the less volatile one is on the right) the more complicated shown above does do it with some modifying not quite like i would want it (it puts the value at the bottom of the cells that would have been merged (I un-merged them but it should work).

Excel question.jpg

[jhren]

Sorry, my bad... I was just typing without actually doing in Excel. It's missing a parenthesis...
=INDEX(A:A,(ROW(A1)+2)/3)