Automatically updates score sheet LIVE
I have attached a sample. I tried to do it but can't so need your expertise in completing my small project. I have been keeping scores of my punters entering their sweep with me but it takes a long time for us to manually count the scores on a daily basis. So I am trying to put it into excel.
I have written what I need done on the excel file with some samples done.
Would appreciate your assistance and expertise.
Thank you
Please populate with numbers, not RANDBETWEEN, and show some expected results. For some races, the use of RANDBETWEEN gives SILLY answers.... such as: Horse X came in first and third in the same race.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi Glenn, thanks for your prompt response. I have updated as requested. Thank you
The second sheet is still full of RANDBETWEENs and you DID NOT supply expected answers. This is a guess.
In X3, copied across and down.
=IFERROR(LOOKUP(IF(INDEX(RESULTS!$C4:$L13,1+INT((COLUMNS($X3:X3)-1)/3),1+MOD((COLUMNS($X3:X3)-1),3))=INDEX($B3:$K3,,1+INT((COLUMNS($X3:X3)-1)/3)),1+MOD((COLUMNS($X3:X3)-1),3),0),{1,2,3},{5,3,2}),0)
Sorry.
now I have deleted all others except for some. I just wanted the Cell X4 onwards to be pasted just like what I did but done faster than manually doing it for every cell.
Thanks
I do NOT believe your expected answers are correct. They refer to cell B3 all the way across from X3 to BA3. Please CHECK the results of the formula that I gave you at post 4.
Aha... some $ missing from my efforts. Try it now:
X3, copied across and dnown.
=IFERROR(LOOKUP(IF(INDEX(RESULTS!$C$4:$L$13,1+INT((COLUMNS($X3:X3)-1)/3),1+MOD((COLUMNS($X3:X3)-1),3))=INDEX($B3:$K3,,1+INT((COLUMNS($X3:X3)-1)/3)),1+MOD((COLUMNS($X3:X3)-1),3),0),{1,2,3},{5,3,2}),0)
I manually checked a few results (different shading)
Last edited by Glenn Kennedy; 09-16-2023 at 08:54 AM.
I tried it but doesn't seem to be working properly I get an error message opening it and if I changed the selections under cell E4 to reflect the winning horse as shown on E3 it doesn't change the points at cell X4 to reflect 5? Maybe its because of the error I get when opening?? I have attached the error I got. Maybe cos I am using Excel 07?
Also the cell BD3 onwards do I use the same formula you made on these as well?
I don't see the error. set that aside for a moment.
Selections E4 refers to race 4 at Venue 1. the person guessed/picked horse 3 (blue cell). On the results sheet, race 4, Melbourne was won by Horse 3 (blue cell). That person should score a 5. They did, AG4, in selections... blue cell.
What's the problem? If that's NOT what you were expecting to see, then you need to explain CLEARLY what you expect to see and where you expect to see it. You did NOT respond to my comment about B3 being used all the way across the sheet. Was that a mistake by you??
The error seems to relate to a comment. Not sure... I deleted all comments/notes from this file.
Forget BD3 for the moment!! Lets get the first one working first.Originally Posted by fintakol
Okay when you show your example, correct that is how its supposed to work. So I guess its my error that is causing me not to get the same results. I tried changing that same cell you mentioned to a losing number but nothing changed, the points stayed same as 5.
can you save as old excel file so compatible to my excel 2007
OK. It works for me... If I change E4 to 333, then a ZERO appears in AG4.
One possibility is that the formula is an array formula in your Excel version. Does it appear with {} around it?
I did NOT change the file type. You used an XLSX... it is still an XLSX.
Also check that calculation options are set to automatic...
yes I think it shows see attached
Attachment 842883
How do I do this?
ok I seen it and it is automatic as shown belowAttachment 842884
TBH, I can't remember how to do that in Excel 2007. Formulas/calculation options on later versions....
What happens if you change the 333 back to 3. What is in cell AG4? Then click on AG4, are the {} there? arrays can be very tempremental!!
When changed as suggested nothing changes and cell AG4 stays the same and it does show the {} still.
Can you save this same file but try to Save as "Excel 97-2003
I twiddled with the syntax. Swap E4 between 3 and 333. Any better?? What do you see in X14 to X17 in each case?
Excel 97-2003 is not needed for Excel 2007. It may, indeed, make things worse... I have almost NO recollection of Excel 2003, as back then I was a total beginner... A lot of things may fall over in .xls format.
Attachment 842887
this is what I see. Is this what you asked for?
I played with the same set of numbers and nothing seems to be changing?
Ok I will find another version just incase it solves my issue. I believe you solved what I asked for. How about the rest for Venue 2 I can copy and paste the same over there?
and the numbers DO NOT change if you replace the 333 with 3???
no they dont see attached
Attachment 842889
I will check back later, need to sleep now its very late. Thank you for your help.
Here is is with the latest version covering both venues. Totally weird...
That's very helpful.... I now know WHERE something is going wrong.
It's almost beer o'clock here. I'll ruminate on this for a while.
When you surface in the morning.... another version to look at. This is doing my head in...
Do the 3 - 333 toggle thing and report back...
Sorry couldn't help myself just had to check again.
I am attaching all the issues I get when opening the file
first issue Attachment 842892
second issue Attachment 842893
and I opened the file and toggled the 333 to 3 and still nothing, sorry see attached Attachment 842894
I'm going off to become an alcoholic... That damn error again. What is it doing to the sheet???
I tried running stellar repair for excel on it and even the software says 'unable to load MS Excel file'??
Go on, go and have a few and come back fresh. its way past witching hour here. Gnite.
Good morning, I am back and wondering if anything new to my issue here? I am still trying to upgrade my excel software from 2007 to 2016.
Anything?
Re: Automatically updates score sheet LIVE
GOOD NEWS GLENN.
I tried it all over again starting from the beginning and using your formula and it worked great!!
I think somewhere along the line the file got corrupted. I am attaching a copy of the one that is working fine on my excel.
Is it possible to have excel change a color live? Example if the selection wins to change color to yellow, 2nd green and so forth?? I don't think so right? :D
Anyway THANK YOU!!!
Were you expecting Glenn to be working on it overnight?
Have you upgraded? If so, please update your forum profile.
Manually mock up the shading you want.
Last edited by AliGW; 09-17-2023 at 02:33 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Tryit now, with CF applied.
Hi Glenn,
I'm not sure but now I'm getting the same error message? Can you paste the formula you added so I can do it manually from my side with my excel version, thanks.
which same error message?
=B3=INDEX(RESULTS!$C$4:$C$13,COLUMNS($B3:B3))
=B3=INDEX(RESULTS!$D$4:$D$13,COLUMNS($B3:B3))
=B3=INDEX(RESULTS!$E$4:$E$13,COLUMNS($B3:B3))
for first, second & third. But to repeat... which error message?
This same like the first issue I was telling you see attached.
Attachment 842945Attachment 842946
Sorry Glenn, I'm lost with what you asking for here?
Those are the 3 formulae I used in conditional formatting to colour the cells as requested....
Ok, so in the excel file I downloaded from you didn't have anything like that showing up anywhere? Which cell should I check at?
I am attaching what I have done so far with my excel. I have added at the results tab a automated sorting to show who is leading after every race. So probably the color formula won't be necessary after all, sorry about that.
But as i said I only need to complete the Venue 2 pointing system, thats where I'm stuck now.
All the columns colored yellow will actually be hidden from view so it looks neater after all formula has been corrected.
Thanks again.
Sorry tried attaching my excel but kept failing to?? I noticed now that the size is 5MB, so I tried deleting cells with formula all the way down to only a maybe 10 lines but file still 5MB?
I guess the Rank formula and the auto sort uses a lot of memory?
I deleted something and now its all good I can upload. Please see updates done so far.
Try this for Venue 2. I also changed the Venue 1 formula to show blank when the race had not taken place.
Still getting that error message?
Not sure why but it seems I always get the same error when opening your files?
I copied your formula to my other file and it worked all of it. GREAT!
Thanks a million.
Now I need to populate the rest and test it out live.
OK.
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks