First Thread - Sorting and cell references

[TomW23]

Hi all,

Brand new here!
I'm pretty much self-taught with excel so my formulae and methods are probably a little bizarre. I make spreadsheets for football (soccer) stats and I love improving and adding to them. I make graphics with the data I create for blogs I write for.

I have a sheet that totals up all the data for each player that calculates from the other sheets that collect the data for each category.
I also have another sheet that automatically (in theory) creates the graphics. The problem I have is that I have to resort the players page every time I create a graphic for each category so the players are listed in order. But I have to remember to reorder it each category otherwise the data is in the wrong order. Is there any way I can automate this process to save time and avoid mistakes? Ideally I'd like to be able to map the formulae in the graphics table to the specific player's row in the players sheet.

Sorry if I'm being unclear, please let me know if you need any further info/clarification.

Thanks,
Tom

[Glenn Kennedy]

Too many words and not enough detail!!!

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[TomW23]

Here's a quick example

[Glenn Kennedy]

For example (for assists),in G4 copied down, an array formula:

=IFERROR(INDEX(Players!$A$2:$A$15,MATCH(0,IF(Players!$D$2:$D$15=H4,COUNTIF(G$3:G3,Players!$A$2:$A$15)),),),"")

and in H4, an ordinary formula:

=LARGE(Players!$D$2:$D$15,ROWS(H$4:H4))

similarly for minutes and goals, as in the attached sheet.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[TomW23]

Hi,

Thanks for the response. It's working on most of the calculations but for one, the right hand column with totals is correct, but the left hand one with names is not including all players and just repeating some. How can I fix this?

Cheers

[JeteMc]

I compared the manually input results from the "Desired" sheet in the file attached to post #3 to the results yielded by Richard's formulae and they match exactly.
Therefore it would be helpful if you could upload a sample illustrating the scenario(s) in which players are not included or repeated.
Let us know if you have any questions.

[TomW23]

Thanks again. The formula works great for goals and assists as I just copied it over. It's probably my failure to adjust it to the different stat, I've never used this type before! I've attached a copy with just the raw numbers with names removed. It shows on the 2nd sheet that the Goals and Assists is working, just not the Yellows and Reds.

[JeteMc]

The issue is the COUNTIF in J4 is starting with J2 instead of J3. The formula should read:

Please Login or Register  to view this content.

Same issue is occurring in M4.
Let us know if you have any questions.

[TomW23]

Thanks, got that working now!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.