I think this needs further explanation on my part- while it was never meant to in the first place, when the path was shown, it at least referenced the workbook the formula was in (i.e. the same workbook) which meant that, while cluttered, the formulas still worked. Now it is not only inserting an unwanted file path, but one for a completely different workbook in a completely different location.

I suppose an example may be helpful:

formula I want and entered in the first place:

=100-Y1096

formula I used to get:

=100-'C:\Users\jonfry\Microsoft\Excel\K2 Kiln Data 26-8-14.xlsb'!table1[[#This Row],[total waste %]]

formula I know get (referencing a copy of the workbook in a different location):

=100-'C:\Users\jonfry\AppData\Roaming\Microsoft\Excel\K2 Kiln Data 26-8-14 (version 1) (version 1).xlsb'!table1[[#This Row],[total waste %]]

Now imagine a longer formula, this is a nightmare.

I have noticed that if I manually type in cell data into formulas rather than click onto them, it behaves itself.