Someone emailed us a question on this:
“As an item is keyed on the Master sheet under specific criteria it will automatically enter on the other sheet. In my case soccer is divided into divisions(U6, U8, U10 etc). If a player is entered as a U6 player I want the columns data to go to the U6 sheet. I have included the sheet I am working on. Maybe you can help?”
Here’s a link to the file:
And the solution to key inside the “U6” cell A2 is an array formula:
=INDEX(‘Master List’!A$2:A$28,SMALL(IF(‘Master List’!$E$2:$E$28=MID(CELL(“filename”,$A$1),FIND(“]”,CELL(“filename”,$A$1))+1,3),ROW(‘Master List’!$E$2:$E$28)-1),ROW(A1)))
This must be pasted inside the formula bar, and confirmed with CTRL + SHIFT + ENTER. You should see curly brackets surrounding it when done correctly. The formula will refer to the “Main” worksheet, look for “U6” (represented by the formula MID(CELL(“filename”,$A$1),FIND(“]”,CELL(“filename”,$A$1))+1,3) in Column E, & extract the individual records. Copy down & across.