Referencing every nth cell in a new column

  • Thread starter Thread starter AmyP324
  • Start date Start date
A

AmyP324

I have data in E4, E12, E20... that I want to copy into a new column without
the extra rows in between. How can I do this?

So far I've tried entering =E4 in the first cell, then =E12, and then using
the fill handle. Didn't work :( I'm not very good at excel so please be
specific if you talk about coding or heavy duty formulas.
 
I have data in E4, E12, E20... that I want to copy into a new column without
the extra rows in between.
Click to expand...

Here's one way.

If the new column starts at row 1, put this at the top and copy
downward:
=OFFSET($E$4,(ROW()-1)*8,0)

If it starts at row 2, put this instead:
=OFFSET($E$4,(ROW()-2)*8,0)

Some of the data cells might be blank, in which case the formula
produces zero. If this is troublesome, try
=IF(OFFSET($E$4,(ROW()-1)*8,0)="","",
OFFSET($E$4,(ROW()-1)*8,0))

Modify to suit.
 
Back
Top