Forum Discussion
Excel: How Do I Sort Data in One Tab Without Changing the Linked Data in Another Tab?
I have a 'master tab' where I will be entering data into cells in a TABLE. I have subordinate tabs. The subordinate tabs have formulas that populate cells with a mirror of the data that is in the master tab.
The problem comes when I SORT the table in the master tab. Because it changes the absolute position of the data, the reporting to the other tabs changes.
For example, cell B26 in the Master Tab might say 'Stethoscope'. In another tab, one of the cells also says 'Stethoscope' because the formula for that cell is 'Master Tab'!B26. When I sort the columns in the master tab, the new B26 will be something different; however, I need the cell in the subordinate tab to still read 'Stethoscope'.
Can someone help me with this?
Ben, it's better to add an ID for your records in Master table and INDEX/MATCH on it from subordinate ones
Thank you Sergei. I have not used that function before. Can you refer me to some simple instructions for its use?
- Ben
Hi Ben,
INDEX/MATCH is combination of two functions and together they are some equivalent of VLOOKUP(). In brief MATCH returns the position of the lookup value in the lookup range and INDEX returns the value of the cell on that position. In more details
https://exceljet.net/index-and-match
https://corporatefinanceinstitute.com/resources/excel/study/index-match-formula-excel/
and in many other places.
Attached is the small sample of how it works, both if you use tables with structured references and if you use ranges.
