Problem with column including vlookup in table not sorting accurately

[Festivalgeek]

Hi - hope someone can help me as my brain is aching with trying to work this one out!

I have a large table with loads of columns - many of them including text but a few of them are vlookup formulae. When I sort the table on one of the text columns, the columns with the formulae in them go wrong.

As an example, one of the formulae is:

=(VLOOKUP('Sheet 1'!$D2,Table10,2,FALSE))

Columns A to C are text. If I now sort the table on column A to get it in alphabetical order, the formula shown above changes to read

=(VLOOKUP('Sheet 1'!$D8,Table10,2,FALSE))

BUT the problem is that the formula is physically still located in row 2 - so it is bring back the correct result but not putting it in the right place.

My research suggested that the problem is that sorting only works on text and that maybe using the INDIRECT function would solve it. So I tried:

=(VLOOKUP(INDIRECT("D2"),Table10,2,FALSE))

But the problem with this is that when the table auto fills, it just copies this exact formula down whereas I need the D2 to become D3, D4 etc.

Can any one help please?