Feb 25 2020 08:06 AM
Good morning,
I am a track and field coach, and I am trying to create an all-in one spreadsheet that will save me time when making rosters.
I have attached a fake roster where I have a list of names and the event each kid would be competing. My roster is in alphabetical order and I figured out a way to get a count of the number of athletes in each event (right side of the spreadsheet), so that I know how many kids I will have in each event.
However.....My question is is there a way to search for all athletes who are competing in the 100 and then move the name associated with it to the bottom of the sheet where it would show ALL athletes who are running the 100 underneath that slot of the spreadsheet instead of copying and pasting by hand? The same would be for each event. I am looking for a formula that can search for "100" and then move all the names that have "100" in their row as an event to the column at the bottom of the sheet under the 100 tab.
That way, as a timer, I can see who all is running the 100m dash all in one place instead of having to search by name to see what kid is doing what event.
I hope this makes sense and I am looking for help in such a bad way!
Thanks in advance!!
Feb 25 2020 08:16 AM
I'm sure there are other ways to do this (Excel always has multiple ways to skin the cat), but the simplest and most straight-forward is to set your roster up as a table and use the Data...Filter capability. I've done that here for the folks running the 100. All of the others are still there, just filtered out of the view for now.
See those little carets at the top next to each heading? Click on the caret for "Event" and you can change the filter.
By the way, your spreadsheet would be less liable to confusion if you took all that stuff off to the right, and down below under the roster...separate that stuff into separate spreadsheets. The COUNTIF function, along with any others you have used (good for you!), will still work even if it's on a separate sheet.
Feb 25 2020 08:41 AM
I used that data --> filter suggestion that you said and it worked IF the 100 was listed as Event 1. If the 100 was under the Event 2 column, it would not appear as part of the filter....am I missing something?
Also, I agree with you on moving the stuff on the far right of the page to another spreadsheet, but I do not know how to use the countif function from one spreadsheet to another.......I just set the print area so that part of the spreadsheet does not show up.
Any help is much appreciated!
Feb 25 2020 08:57 AM
It's less complicated than it might appear.
So dealing with your two questions:
Feb 25 2020 08:59 AM
If you're using Office 365 this is the way to go.
Feb 25 2020 10:41 AM
First, I figured out how to copy and paste into other sheets, thanks so much!
But, back to my main question...the way I have this spreadsheet setup, is there no way to compute what I am looking to do (if the same event is in different columns due to how I presently have the roster sheet constructed)....
Or, is there another way to format it that I am not aware of to make this work and simply things for roster creations?
Feb 25 2020 11:42 AM
You got an answer from @Patrick2788 which looks like it definitely works. My concern about that is that it's a fairly advanced methodology that I've not used. Why don't you start with that, if it does indeed give you a way to move forward.
Let's see what others come up with. I believe that some variation on Power Query would work for you as well--unfortunately, my version of Excel doesn't seem to have that capability so I'm not able to offer guidance.