Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-694889%22%20slang%3D%22en-US%22%3Ehaving%20trouble%20with%20expanding%20my%20array%20formula%20to%20new%20entries%20on%20the%20main%20tab%20to%20show%20on%20sub%20tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694889%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI've%20been%20working%20on%20a%20formula%20to%20extract%20data%20from%20the%20master%20sheet%20on%20excel%20to%20sub%20sheets.%20I've%20got%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Ethe%20formula%20to%20work%20but%20when%20I%20enter%20new%20teams%20on%20the%20master%20sheet%2C%20it's%20not%20extracting%20to%20the%20sub%20sheets%20properly.%20it%20creates%20a%20%23number%20error.%20What%20am%20I%20doing%20wrong%3F%20Please%20look%20on%20the%20tyke.novice%20tab%20to%20see%20the%20problem%20i'm%20commenting%20on.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EIF(ROWS(%24A%243%3AA5)%26gt%3B%24J%241%2C%22%22%2C%3C%2FSPAN%3E%3CSPAN%3EINDEX(%5BYHLRegistation%5DMaster!%3C%2FSPAN%3E%3CSPAN%3EA%242%3AA%2419%2CSMALL(IF(%5B%3C%2FSPAN%3E%3CSPAN%3EYHLRegistation%5DMaster!%24B%242%3A%24%3C%2FSPAN%3E%3CSPAN%3EB15%3D%24C%241%2CROW(%5BYHLRegistation%5D%3C%2FSPAN%3E%3CSPAN%3EMaster!%24B%242%3A%24B15)-ROW(%5B%3C%2FSPAN%3E%3CSPAN%3EYHLRegistation%5DMaster!%24B%242)%2B1%2C%3C%2FSPAN%3E%3CSPAN%3E%22%22)%2CROWS(A%243%3AA5))))%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3Ethat%20is%20my%20formula.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-694889%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-695307%22%20slang%3D%22en-US%22%3ERe%3A%20having%20trouble%20with%20expanding%20my%20array%20formula%20to%20new%20entries%20on%20the%20main%20tab%20to%20show%20on%20sub%20tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-695307%22%20slang%3D%22en-US%22%3EThis%20seems%20to%20be%20an%20array%20formula.%20Press%20F2%20and%20then%20control%2Bshift%2Benter%20to%20make%20it%20work.%3C%2FLINGO-BODY%3E
cullenrevel
New 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.
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies