Finally managed to get to the bottom of this horrible Excel bug.
When trying to use the Excel Advanced Filter I would constantly get the “extract range has a missing or illegal field name” error message which meant very little until I found this post.
In a nutshell I fixed this by:
a) making sure there wasn’t a space between the data and the header row
b) putting the destination data in completely empty cells
Not sure exactly which part of a) and b) was responsible for the fix but after spending the best part of half an hour I wasn’t in the mood to spend more time debugging Microsoft software.