OK, first, this applies to a regular, unformatted table and to the "special" formatted, database-style tables in Excel 2010.

Let's say I have a table with 2 columns. Column A has text names and Column B has a formula referring to column a - eg. Trim(A2)

Sometimes, if I sort, say, column A in a table in a different order, I find that the formulas adjust. So regardless whether my names are in ascending or descending order, cell B2 always says Trim(A2), it has different values depending on how column A is sorted.

But sometimes I notice in tables that when column A is sorted, the formula will move as well. As an example, the formula in cell B2 (which should say Trim(A2) now says Trim(A35) and gives the wrong value for the entry in column A. This won't necessarily happen in this specific example, but I've noticed that it happens with some of my longer formulas contains IF statements and/or VLOOKUPS.

As result, I have to be REALLY careful in sorting tables with formulas and I don't know why.

Can someone please explain to me why sometimes the formulas stay and sometimes they move?!? It's maddening...