Forum Discussion
Create a ranking
You may still be reeling so I have had a first cut at incorporating your data
Dragging the corner of the matches table down should incorporated additional matches as would adding further data. The stats columns could be added as required.
Hi Peter,
You've nailed it in terms of getting the ranking to work.
Now I need to understand enough to be able to transpose that onto my sheet. That's the tricky bit ;
You'll have guessed I'm not a coder (!), so being use to standard excel lookups as defined by cell or column references, this seems like a different world.
Some questions......
How do I see the instruction that says what the initial ranking table is, and how do I adjust that to be a column on a different sheet in the same workbook?
When I change the results box, and add more results, the current ranking in Cell O returns to "Name", and no names; and nothing I do returns the original list.
I've created a onedrive link (there is nothing else in my One drive, so hoping no back door alerts are going to go off on my PC) https://1drv.ms/f/s!Avf59RbR7DWbjhOzT6WIc0CI2xRF?e=WJwgjf
Thanks, James
How are you getting on? I have added some of the match analysis contained in your workbook. I normally perform calculation using dynamic arrays rather than tables but there is no reason to pull you any further out of your comfort zone! I haven't used a direct cell reference or non-array formulas for 7 years now (it used to be CSE and was horrible) so my methods are not exactly mainstream (yet?).
Besides listing the competitor match analysis presented in initial rank order, I have provided an alternative version using the latest rank order.
Hi Peter,
Many thanks for the updated sheet - 2 steps forwards for sure and it's got everything included that's needed. I'm being beaten by tech wizardry though, as I mentioned in my last post: If I enter any additional match result rows, or in this case not adding a row, but just including results in row 17 between Martin and Charlie, or change a result, the calculations in columns G & H return blank on the Matches tab, and the Current Ranking table contents on LMTL show an error #Name! (Please see attached).
Final question - to share this in real-time (I would normally work in Google Sheets for sharing ease), what would your suggestion be (assuming attaching to an email wouldn't work for the way people are interacting (whattapp and Google) - Most Excel functions that I come across are transposable to Google, but uploading this to google raised errors. You might not be familiar with Google sheets, but I thought it would be worth asking in case you knew.
Many thanks,
James
I think I have successfully crashed a working copy of the Excel workbook into you OneDrive folder.
(I also told Microsoft how lousy the Excel online experience is for development, given that I never reference data other than by defined name and usually employ named Lambda functions!)
I think the online experience could offer a workable sharing environment provided it is possible to sort out data protection at a granular level (otherwise the winner's laurels would always go to your most dishonest member!)
- Hi Peter, I have replied in the onedrive file, but C&P here as well.
This is based on the above file Tennis ladder – Peter V1: I've been able to update a score, but the errors still appear as before Peter (#NAME! Etc..). As you said before, you're on 365, and I'm on an older setup (2019 version 2312). If this is now about compatibility of versions, then MS wins... SO frustrating....
Things seem to have gone wrong in the 'simple' structured references in the MatchTable which is throwing #NAME! errors before one even gets to the ladder calculation step where the 'magic' happens.
The working formula that calculates the Result column on my copy is
= LET( completed, OR(MatchTable[@[Sets1]:[Sets2]] = 2), result, IF([@Sets1]>[@Sets2], "Win", "Loss"), IF(completed, result, "") )I could equally have used
= LET( completed, OR([@Sets1]=2, [@Sets2]=2), result, IF([@Sets1]>[@Sets2], "Win", "Loss"), IF(completed, result, "") )and, to be honest, I think that is clearer. The important thing with the table is that the formulas must propagate down the entire column and become its default formula.
As for sharing, I have relatively little experience of that. You have shared the OneDrive version of the file and that could offer a way forward. In corporate environments SharePoint is widely used. Perhaps once we have got your version of the spreadsheet up and going, you could post a supplementary question on the Forum relating to the collaborative use of spreadsheets and hope to get a wider response.
A further thought. If you update the OneDrive version of the file to the version that has gone wrong, I may be able to work on it and correct any issues. That would, however, be as much of an experiment for me as for you!
Thanks Peter - I've added the new code into the results column though without success. (screen grab attached).New code inc.