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

Discuss the spreadsheet application

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

Postby blueboss » Thu Mar 07, 2019 5:10 pm

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
Attachments
FollowCell.ods
(19.08 KiB) Downloaded 10 times
Last edited by Hagar Delest on Fri Mar 08, 2019 10:24 pm, edited 2 times in total.
Reason: tagged solved
OpenOffice 4.1.1
Windows 10
blueboss
 
Posts: 6
Joined: Wed Mar 06, 2019 3:07 am

Re: Help cell A = cell B that gets sorted and now no longer

Postby MrProgrammer » Thu Mar 07, 2019 5:57 pm

Hi, and welcome to the forum.

blueboss wrote: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
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3840
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Help cell A = cell B that gets sorted and now no longer

Postby blueboss » Fri Mar 08, 2019 4:38 am

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
Attachments
FollowCell-v2.ods
(19.78 KiB) Downloaded 9 times
OpenOffice 4.1.1
Windows 10
blueboss
 
Posts: 6
Joined: Wed Mar 06, 2019 3:07 am

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

Postby MrProgrammer » Fri Mar 08, 2019 5:52 pm

blueboss wrote:I have a new question:
A new question should have a new topic. One issue, one thread

blueboss wrote: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.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3840
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

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

Postby blueboss » Sat Mar 09, 2019 11:14 pm

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
OpenOffice 4.1.1
Windows 10
blueboss
 
Posts: 6
Joined: Wed Mar 06, 2019 3:07 am


Return to Calc

Who is online

Users browsing this forum: Villeroy and 30 guests