I have defined my data (set of hazards set out 20 rows x 4 columns) as 'myhazards'
=INDEX(allhazards,1+INT((ROW($A1)-1)/COLUMNS(allhazards)),MOD(ROW($A1)-1+COLUMNS(allhazards),COLUMNS(allhazards))+1)
I drag this down and A1 changes to A2, to A3 etc. to get the column of elements
I'm listing them from this:
a1 a2 0 a4
b1 0 b3 0
to this:
a1
a2
0
a4
b1
0
b3
0
However I don't want the cells to be listed if their value is 0 (zero) i.e. I want them to be like this:
a1
a2
a4
b1
b3
How do I modify my function line to get it to do this? Cheers!
=INDEX(allhazards,1+INT((ROW($A1)-1)/COLUMNS(allhazards)),MOD(ROW($A1)-1+COLUMNS(allhazards),COLUMNS(allhazards))+1)
I drag this down and A1 changes to A2, to A3 etc. to get the column of elements
I'm listing them from this:
a1 a2 0 a4
b1 0 b3 0
to this:
a1
a2
0
a4
b1
0
b3
0
However I don't want the cells to be listed if their value is 0 (zero) i.e. I want them to be like this:
a1
a2
a4
b1
b3
How do I modify my function line to get it to do this? Cheers!