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 depend on Sheet1 cells. But if I assign some sorting to Sheet1, all the values of dependent cells in Sheet2 are mixed. How to avoid that? I.e. how to make cells depend not only on the cell index (A1, B2, etc.) but also on the values themselves?
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?
