adding new rows on source sheet

Originally Posted by oeldere

Advice

1) Don't work with merged cells, you get in trouble with it, sooner or later

2) Make an helpcolumn to determine if the student is a boy or a girl.

After that you can analyse the data with an pivot table.

I Already unmerged the cells, thanks for that...

I wish I could play with the SOURCE sheet to place helper columns for it, however, I couldn't because the source sheet did not come from me.

Originally Posted by oeldere

Then make the helpcolumn on the end of the file.

Or

Talk with the "owner" of the source sheet (to change the format).

Previously, I was using a code which I thought would work on the girls' list using an array function which was shared here in the forum before...

=IF(ROWS($46:47)>MAX(IF(ISNUMBER(SEARCH(SUBSTITUTE(B$44,"STUDENTS",""),'INPUT DATA'!$B$11:$B$1000)),ROW('INPUT DATA'!$B$11:$B$1000)-MIN(ROW('INPUT DATA'!$B$11:$B$1000))+1,""))-MAX(IF(ISNUMBER(SEARCH(SUBSTITUTE(B$7,"STUDENTS",""),'INPUT DATA'!$B$11:$B$1000)),ROW('INPUT DATA'!$B$11:$B$1000)-MIN(ROW('INPUT DATA'!$B$11:$B$1000))+1,""))-1,"",IFERROR(INDEX('INPUT DATA'!$B$11:$B$1000,MAX(IF(ISNUMBER(SEARCH(SUBSTITUTE(B$7,"STUDENTS",""),'INPUT DATA'!$B$11:$B$1000)),ROW('INPUT DATA'!$B$11:$B$1000)-MIN(ROW('INPUT DATA'!$B$11:$B$1000))+1,""))+ROWS($46:47)),""))

I'm really not sure why it doesn't work anymore.. the function was supposedly going to recognize the "FEMALE STUDENTS" which are directly below it and pull it out without recognizing the lists above it (i.e. the boys' names).