About the best you can do without getting into some more complex formulas, is to make sure the row remains blank:
Formula:
=IF(OFFSET($A$1:$B$2,TRUNC((ROW()-ROW($A$10))/COLUMNS($A$1:$B$2)),MOD(ROW()-ROW($A$10),COLUMNS($A$1:$B$2)),1,1)="","",OFFSET($A$1:$B$2,TRUNC((ROW()-ROW($A$10))/COLUMNS($A$1:$B$2)),MOD(ROW()-ROW($A$10),COLUMNS($A$1:$B$2)),1,1))
Which would return
1
3
4
otherwise, you would probably have to use an ARRAY FORMULA and they are easier to develope if we can actually see a example of the data we are working with (see signature below for uploading a sample workbook)
Hope this helps
Bookmarks