If cell is 0, reference next row of cells

I'm listing 50 rows x 8 columns of cells (defined 'allhazards')

However each cell in myhazards is referencing other sheets and contain 0's where there is no text to be referenced.

When I list myhazards in a single column using this formula:

=INDEX(allhazards,1+INT((ROW($A1)-1)/COLUMNS(allhazards)),MOD(ROW($A1)-1+COLUMNS(allhazards),COLUMNS(allhazards))+1)
(then drag down the column to get all of the cells from 'allhazards')

How do I implement this:

if cell in 'allhazards' is 0, do not reference this, move to next row
...then reference next row's columns until cell is 0, then move to next row
...keep doing this until there are no rows left to be referenced

eg. if 'allhazards' contained these cells (eg. 2 rows x 8 columns):
hello how are 0 0 0 0 0
good 0 0 0 0 0 0 0

It should produce this:
hello
how
are
good

but not this:
hello
how
are
0
0
0
0
0
good
0
0
0
0
0
0
0

I've made a quick example with the help of helper cells - see attached below.

Basically converts your allhazards matrix into a series of
1, 2, 3, 4;
5, 6, 7, 8
I call this the Item#
Cell D6: =SMALL(INDEX(COLUMN(allhazards)-MIN(COLUMN(allhazards))+1+COLUMNS(allhazards)*(ROW(allhazards)-MIN(ROW(allhazards)))+(allhazards=0)*1000,0),C6)
Note: C6 is 1, C7 is 2, C8 is 3, etc.

then get's rid of the ones that are 0 - and uses SMALL to identify the item of significance and convert the item# into rows/columns for use in INDEX.
E6: Row_num: =TRUNC(D6/COLUMNS(allhazards))+1
F6: Col_num: =MOD($D6,COLUMNS(allhazards))
B6: Output: =IFERROR(INDEX(allhazards,E6,F6),"")

Should be fairly easy to make this into a mega formula yourself.

Also, cross-post: http://www.mrexcel.com/forum/excel-q...-next-row.html
and really similar and related post: http://www.excelforum.com/excel-prog...-next-row.html