No, you're right - they need to be in the same workbook. The edit relationships is for blending - perhaps that could get you what you want, but proper joins, creating one source are much cleaner.
Perhaps you could create a master Excel file that links to the other sheets? http://office.microsoft.com/en-us/excel-help/create-an-external-reference-link-to-a-cell-range-in-another-workbook-HP010102338.aspx
I would imagine this would get unwieldy.
If the data doesn't change perhaps you could use RDBmerge to put the books and sheets together in one workbook: http://www.rondebruin.nl/merge.htm
Thanks for the quick reply. I have moved everything into one workbook and it is working fine. (I can look for a more subtle approach later).