If a cell has a text, how to move the cell associated to it to a new location (T&F Coach)

Copper Contributor

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!!

6 Replies

@Joedogg555 

 

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.

@mathetes 

 

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!

 

 

@Joedogg555 

It's less complicated than it might appear.

 

So dealing with your two questions:

  1. On the data filter for events 2, 3 and so on, because you have them as separate columns, you have to use the filters one at a time. So to filter only for the 100 event as Event2, make sure that all the other columns are showing "All"--as I've done in the attached. [It is possible to use multiple filters at the same time, but they operate in conjunction with one anther; that's why so long as event 1 was already set to "100" it would be self-defeating to also ask for "100" in event 2, unless there's a single runner doing it twice.]
  2. All you needed to do was open other sheets and copy and paste the other data to those other sheets. The formulas took care of themselves, and (in the process) serve to illustrate for you how you can refer to data on sheet 1 in formulas in other sheets.

@Joedogg555 

If you're using Office 365 this is the way to go. 

@mathetes 

 

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?

@Joedogg555 

 

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.