[Solved] Cell A = cell B sorted and now no longer =B

[blueboss]

Spreadsheet is attached

I want to learn how to make the cell follow another cell wherever it moves to ,

I have a table of 28 players with each player getting scores and then totaled each week
After totaling, the list is sorted for rank decending

Then another table in same sheet I have 4 teams with 7 players each

I populate each players score in Team table by ie. =E63, but after I sort the rank table, the players score is not following remains the same cell, and does equal the new position after sorted.

I want to earn how to make the cell follow another cell wherever it moves to, for eample after being sorted

I have attached the sheet

thanks

[MrProgrammer]

Hi, and welcome to the forum.

I want to earn how to make the cell follow another cell wherever it moves to, for eample after being sorted

Thank you for the attachment. Data → Sort does not (and cannot) update dependent cell references.
Formulas Don't Adapt to Sorting Their Reference Cells

The solution is to use VLOOKUP with the player's name as a key. In Cell E39 instead of =E63 use =VLOOKUP($C39;$C$48:$H$75;COLUMNS($C39:E39);0). This locates Don Bell in the table below and returns the appropriate data. You can copy that formula and paste it into D35:E41 and into G35:G41. Copy/Paste will update cell references automatically. But you will have to change C Paul Forrester to Paul Forrester in cell C35. Perhaps you can show the captain's name in bold instead of prefixing it with a C.
[Tutorial] VLOOKUP questions and answers

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[blueboss]

Thank you Mr Programmer, works great.

I have a new question:

In the upper cells where teams are listed, I would re order the teams as per standing after seeing their total team score each week.
I think with the new code/cells structure now, that I cannot.
Is there a solution to this this?

I will attach the updated sheet

Thanks again Mr P

[MrProgrammer]

I have a new question:

A new question should have a new topic. One issue, one thread

In the upper cells where teams are listed, I would re order the teams as per standing after seeing their total team score each week.
I think with the new code/cells structure now, that I cannot.

Did you try that? If you need additional assistance you'll need to explain:
• exactly what steps you are taking to "reorder the teams",
• what happened
• what you expected or wanted to happen.

You may find it easier to reorder the teams if you have a block of unused rows below 43. For example, move the data in rows 44:76 to a separate sheet. Or you can cut (not copy) B4:C11 to the clipboard, drag B14:C21, B24:C31, and B34:C41 into position, and then paste the clipboard into the remaining empty cells.

Note that the formulas in D5:D11 are all exactly the same and those same formulas are used in D15:D21, D25:D31, and D35:D41 as well. All you need to move are the team name (Cn4), number (Bn5), and players. You could have those all set by formulas which would take the data from a team table and four cells which specify the position. You could a separate topic to ask about that idea, but there are numerous others already it you search a bit. The INDEX function will be helpful for that situation. You will learn more if you try to do it yourself but we can guide you if needed.

[blueboss]

ok thanks I did now move the cells and all ok.

I thought that because the $ would freeze the cell I was referring to but its all fine, much appreciated Mr. Programmer