good day guys
I was looking for some help am a novice with excel and am looking to explore.
Is there a way in which I can group a range of cells? by this I mean
P Name Weeks 1 2 3 4 5 6 7
1 Player 1 W
2 Player 2 W
3 Player 3 W
4 Player 4 L
5 Player 5 L
6 Player 6 L
This table is going to change I.e. the players positions will go up and down put I want the "W" & "L" to stay with them is this possible?
Thanks in advance.
Fessy
If you are changing the positions by sorting, then you just need to select all of the data before sorting. They will all sort and keep together. If Open Office has it, format the area as a Table, that also will help keep things together.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
thanks for that the thing am looking to do is to try and get this to do it automatically as it will be pulling info from other sheets.
I'm trying to understand what you will be doing? Will you be grabbing a new name from another sheet and inserting it into this one and you want all the other cells in the row to automatically move down? That would require VBA.
hi no its going to be a league table which is getting updated on another sheet and if player 1 wins then it will put a W for win in and if a player loses this will put a L for loss in.
This I have already completed I just want the cells to move with players. i.e. player 1 =a1 cells b1, c1 d1 etc need to move with this player if when info which is already been tested and working and moves the player according to results to say player 1 will now be in a2 and so on
thanks mike
Sounds like you just need to link the other cells to the player name with VLOOKUPs or INDEX/MATCH. Can't be more specific with out an example file to work from (Go Advanced>Manage Attachments)
this is the sheet thanks for this
anyone any idea with this one?
Okay, from what I can see, this sheet shows your final rankings. On another workbook, you are tabluating wins and losses. I'd need to see the other sheet (for purposes of this example, you can make them two (or more) sheets within the same workbook.
Is the other workbook set up the same way? Then for W/L/D you can use something like this formula in C3 copied across and down
=VLOOKUP($B3, [workbook2.xlsx]sheet1!$B$3:$BF$22, COLUMNS($B$3:C$3), FALSE)
Hi mate
this is the attached file i want if possible to update the two red sheet tabs. the form I want to pull from the fixtures page i.e. if plplayers (x) > player (y) player x = W
players (x) = player (y) player x = D
players (x) < player (y) player x = L
the table will keep updating i.e. the positions of the players will move according to the league position.
Thanks
fessy
I'm confused as to why the player's scores on the TWPL Fixtures tab are matching the player number. Am I reading this right?
Hi
thats just so i knew i was pulling the correct cell through to the fixtures sheet.
fes
Okay, from the fixtures, you will pull W/L/D etc to the Form tab? And the Year Positions Tab will give a history of what position each player was in on any given past week?
yeah thats what i was trying to do exactly mate
Okay, this formula is huge! In Form!C3 copied across and down
=IFERROR(IFERROR(IF(INDEX(OFFSET( 'TWPL Fixtures'!$C$3,(C$2-1)*10,1,10,1),MATCH($B3,OFFSET ('TWPL Fixtures'!$C$3,(C$2-1)*10,0,10,1),0)) > INDEX(OFFSET('TWPL Fixtures'!$C$3,(C$2-1)*10,2,10,1), MATCH($B3, OFFSET('TWPL Fixtures'!$C$3,(C$2-1)*10,0,10,1),0)), "W", IF(INDEX(OFFSET('TWPL Fixtures'!$C$3,(C$2-1)*10,1,10,1), MATCH($B3,OFFSET('TWPL Fixtures'!$C$3,(C$2-1)*10,0,10,1),0)) < INDEX(OFFSET('TWPL Fixtures'!$C$3,(C$2-1)*10,2,10,1), MATCH($B3,OFFSET('TWPL Fixtures'!$C$3,(C$2-1)*10,0,10,1),0)),"L","D")), IF(INDEX(OFFSET('TWPL Fixtures'!$F$3,(C$2-1)*10,-1,10,1), MATCH($B3,OFFSET('TWPL Fixtures'!$F$3,(C$2-1)*10,0,10,1),0)) > INDEX(OFFSET('TWPL Fixtures'!$F$3,(C$2-1)*10,-2,10,1), MATCH($B3,OFFSET('TWPL Fixtures'!$F$3,(C$2-1)*10,0,10,1),0)), "W", IF(INDEX(OFFSET('TWPL Fixtures'!$F$3,(C$2-1)*10,-1,10,1), MATCH($B3,OFFSET('TWPL Fixtures'!$F$3,(C$2-1)*10,0,10,1),0)) < INDEX(OFFSET('TWPL Fixtures'!$F$3,(C$2-1)*10,-2,10,1), MATCH($B3,OFFSET('TWPL Fixtures'!$F$3,(C$2-1)*10,0,10,1),0)), "L","D"))), "M")
That final outside IFERROR puts an M for missed game if it's not a W,L or D which makes all future values M. You might want to change that to ""
Seems like this should be a lot shorter but I couldn't come up with one that was automatic.
Chemistb
Thats what I was looking for mate thanks very much. they would only be one thing I would like to change on it if the fixtures have no valve in I.e. the game has not been played to keep the cell empty instead of D is this possible?
fes
To keep it empty, replace the "M" at the end of the formula with double quotes ""
Hi chemistb
I did that as you mentioned it in the first message, thanks for that. what I mean if there is a fixture set up and no scores have been input it shows a "D" from draw and there is no scores input put the fixture has been set up please see attached.
I also looked at the positions sheet which you did I think we were on different opinions for this one. what I would like on this one was to have the position for that week i.e. a number between 1 and 20 for each player depending on position for that week. is this possible? it looks like you have taken the scores from the week
Sorry am a novice on this and been working on it for a few weeks now.
your help is much appreciated.
Okay, try this in C3
=IF(OR(ISNUMBER(INDEX(OFFSET('TWPL Fixtures'!$C$3,(C$2-1)*10,1,10,1),MATCH($B3,OFFSET('TWPL Fixtures'!$C$3,(C$2-1)*10,0,10,1),0))), ISNUMBER(INDEX(OFFSET('TWPL Fixtures'!$F$3,(C$2-1)*10,-1,10,1),MATCH($B3,OFFSET('TWPL Fixtures'!$F$3,(C$2-1)*10,0,10,1),0)))), IFERROR(IFERROR(IF(INDEX(OFFSET('TWPL Fixtures'!$C$3,(C$2-1)*10,1,10,1),MATCH($B3,OFFSET('TWPL Fixtures'!$C$3,(C$2-1)*10,0,10,1),0))>INDEX(OFFSET('TWPL Fixtures'!$C$3,(C$2-1)*10,2,10,1),MATCH($B3,OFFSET('TWPL Fixtures'!$C$3,(C$2-1)*10,0,10,1),0)),"W",IF(INDEX(OFFSET('TWPL Fixtures'!$C$3,(C$2-1)*10,1,10,1),MATCH($B3,OFFSET('TWPL Fixtures'!$C$3,(C$2-1)*10,0,10,1),0))<INDEX(OFFSET('TWPL Fixtures'!$C$3,(C$2-1)*10,2,10,1),MATCH($B3,OFFSET('TWPL Fixtures'!$C$3,(C$2-1)*10,0,10,1),0)),"L","D")),IF(INDEX(OFFSET('TWPL Fixtures'!$F$3,(C$2-1)*10,-1,10,1),MATCH($B3,OFFSET('TWPL Fixtures'!$F$3,(C$2-1)*10,0,10,1),0))>INDEX(OFFSET('TWPL Fixtures'!$F$3,(C$2-1)*10,-2,10,1),MATCH($B3,OFFSET('TWPL Fixtures'!$F$3,(C$2-1)*10,0,10,1),0)), "W", IF(INDEX(OFFSET('TWPL Fixtures'!$F$3,(C$2-1)*10,-1,10,1),MATCH($B3,OFFSET('TWPL Fixtures'!$F$3,(C$2-1)*10,0,10,1),0))< INDEX(OFFSET('TWPL Fixtures'!$F$3,(C$2-1)*10,-2,10,1),MATCH($B3,OFFSET('TWPL Fixtures'!$F$3,(C$2-1)*10,0,10,1),0)),"L","D"))), ""),"")
Chemistb
thats magic my friend thanks again for your help much appreciated.
Mikey
Looks like I'm more than a bit late in this thread but just found it through ChemistB's link in 'Call the cavalry'.
This uses named ranges and custom formatting in an effort to reduce the amount of effort taken to calculate the formula. I did rush this a bit so I expect there is room for some improvement.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks