adding new rows on source sheet

I have a "source" sheet where I list all the names of my students. This source sheet serves as the base reference for my class record. My problem is, when I add a new row or delete a row in my source sheet, it doesn't add a new row or delete a row on my class record sheets. Is there any way I could fix that?

Can you please add a sample sheet?

I think you have a summary on one sheet and the master data on the other. Summary sheet is not getting changed even after changes made on the master data. If that is your issue, please do a refresh all (under Data Tab). If additional rows has been added, place your cursor on Pivot table, an "Option" Tab will appear as a new Tab, click on "Change Data Source" and reselect the whole table.

Here's the template: E-CLASSRECORD_WEDZ.xls

actually, these are my problems...

1. the boys' list of names reflect on the MTB sheet BUT when I add new rows on INPUT DATA sheet... there are no rows added on the MTB sheet.

2. the girls' list are not updating correctly.. it still shows the boys' names.

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).

Oh I got that working already...

Now my only problem is that when I insert a new row on the resource sheet (INPUT DATA), the MTB sheet won't add a new row.. it will only put the referenced name below it, but the grades/scores aren't going down with the owner of the said scores..

Originally Posted by oeldere

You get the faillure with the boys or with the girls?

Did you amend the range in the formula in the MTB sheet?

What do you mean "amend"? hehehe
I don't really get it.. sorry.. i'm not really good at excel...

I was using an Array Function right, so I used it on the table in the MTB sheet.. When I insert a new row on the Input Data sheet, it won't add new rows on the MTB sheet, instead it only copies the new data in the Input Data sheet, thus the scores of the previous student under that row are still there on the new row which is not suppose to happen.

Originally Posted by oeldere

Did you change the range in the formula in the MTB sheet?

Yes, I did... Using the array function...
But it only renders the value of the new row I inserted in the Input Data Sheet and does not move the scores down.