Formula help with next non blank cell

[Cherrypi]

Hi everyone
I'm not great with excel but am trying to learn. I'm a teacher trying to make a spreadsheet to give some feedback to pupils. I have got it to do some question analysis and identify students weak areas based on there responses and am now trying to collate findings. I need excel to return for me a list of weaknesses from cells.

I have got it to return the first non blank cell using this formula:-
=INDEX('Student Weaknesses'!$B2:$R2,MATCH(TRUE,INDEX(('Student Weaknesses'!$B2:$R2<>""),0),))

This works fine but I now need to in the adjacent cell to follow the same rule but to return the next non blank cell after this one. I did manage to get it to return the last non blank cell but can't get it to do the next one. It's driving me up the wall and any help would really be appreciated!

Thanks!

 

[MARK858]

Possibly try the formula below which must be entered with Ctrl-Shift + Enter and not just Enter.

=INDEX('Student Weaknesses'!$B$2:$R$2,SMALL(IF('Student Weaknesses'!$B$2:$R$2<>"",COLUMN('Student Weaknesses'!$B$2:$R$2)-COLUMN('Student Weaknesses'!$B$2)+1),2))

 

[MARK858]

Actually if I am assuming right what you are trying to do then try the formula below and drag across.
Again it must be entered with Ctrl-Shift + Enter and not just Enter (if you have done it correctly it will surround the formula with {}).

=IFERROR(INDEX('Student Weaknesses'!$B$2:$R$2,SMALL(IF('Student Weaknesses'!$B$2:$R$2<>"",COLUMN('Student Weaknesses'!$B$2:$R$2)-COLUMN('Student Weaknesses'!$B$2)+1),COLUMN('Student Weaknesses'!A:A))),"")