Excel formulas pulling from another worksheet changes when sorted

[CC]

Excel spreadsheet has multiple worksheet tabs. One worksheet has formulas
that pull data from another worksheet. This works fine until you sort the
data worksheet which changes the values on the formula worksheet. How can
you keep the data the same and still be able to re-sort the data worksheet?

 

[JBeaucaire]

That would require some information about the formulas that are getting
messed up. If you want to be able to freely sort raw data on one sheet and
the summary sheets USING the data still report properly, you have to
structure the formulas to go and find the data needed.

So, a simple =Sheet2!A3 won't work, sorting will change the entry in A3.

Typically, to keep it working you will use a VLOOKUP or LOOKUP or
INDEX(MATCH() on a piece of data that matches in an adjacent column.

For instance, if you're trying to pull over the Address that matches a Name,
you'd "VLOOKUP" the name and pull back the address from the same row wherever
it's found.

Here's a nice tutorial on using VLOOKUP to find data:

http://www.contextures.com/xlFunctions02.html