having trouble with expanding my array formula to new entries on the main tab to show on sub tabs

Copper Contributor

I've been working on a formula to extract data from the master sheet on excel to sub sheets. I've got

the formula to work but when I enter new teams on the master sheet, it's not extracting to the sub sheets properly. it creates a #number error. What am I doing wrong? Please look on the tyke.novice tab to see the problem i'm commenting on.

IF(ROWS($A$3:A5)>$J$1,"",INDEX([YHLRegistation]Master!A$2:A$19,SMALL(IF([YHLRegistation]Master!$B$2:$B15=$C$1,ROW([YHLRegistation]Master!$B$2:$B15)-ROW([YHLRegistation]Master!$B$2)+1,""),ROWS(A$3:A5))))

that is my formula.

1 Reply
This seems to be an array formula. Press F2 and then control+shift+enter to make it work.