Forum Discussion
Solved
How to keep the values of the cells if their influencing cells are being sorted?
Hello everyone! I have two sheets in Excel, Sheet1 and Sheet2. Since Sheet1 has too many columns, I created a separate sheet to combine different stats about Sheet1 in there. Thus, Sheet2 cells de...
Not sure on which platform/version of Excel you, as variant that could be
=MIN( INDEX(Results!$D$4:$O$9, MATCH($B2, Results!$B$4:$B$9,0), {1,4,7,10} ) ) =AVERAGE( INDEX(Results!$D$4:$O$9, MATCH($B2, Results!$B$4:$B$9,0), {1,4,7,10} ) )
It looks like you use direct reference from one sheet on another. To link values correctly you need any of lookup functions (XLOOKUP, VLOOKUP, etc) if your records have unique ID. Better to discuss on concrete sample.
SergeiBaklan I use Excel 2013, there are no lookup functions in this version, unfortunately. But I guess I can move the file to OneDrive's Excel which has lookup functions, so it can be an option. Simply the case is:
Sheet1 contains the results of a racing series season after each round: lines define drivers, columns define rounds. Each round has 3 subcolumns to define starting position, finish position and amount of points after the round. Sheet2 contains specific stats about the season: lines define drivers, columns define comparison criteria, for example average starting position. Basically, that's it. I just want to always maintain the links between dependent cells in Sheet2 and influencing ones in Sheet1, regardless of which column Sheet1 is sorted by.
Actually 2013 has lookup functions - LOOKUP(), VLOOKUP(), INDEX/MATCH. Perhaps you may share small sample file to make discussion more concete?
SergeiBaklan I would be glad to, but I don't see in the toolbar how I can attach files here
If you don't see this option
you may share the file on OneDrive, GoogleDrive, whatever and share the link. Be sure proper permissions are set.