sorting table by dependent column

Hello,

This is my situation:

I have a table... the firm column contains serial numbers, and the subsequent columns contain values that are obtained from other tables using VLOOKUP (using the serial numbers).

The second column is 'surname', and I'd like to sort the table by surname.

However, when I click 'sort' and choose to do it by surname, it doesn't work as intended - seemingly because the columns other than the first are full of formulae, something strange happens and I don't get the required result.

Is this a common problem? How is it remedied?

Add filter in first row and try use sort in filter

that produces the same result.

to be specific about what is happening:

Say when I sort by surname rows 2 and 5 are meant to swap. This happens, except THE FORMULAE swap too - so the vlookup originally in row 2, referencing A2, is moved to row 5, but still references A2... but the values (serial numbers) in A2 and A5 were swapped by the sort!!