That is consistent with my experience. You can do joins between sheets in an excel workbook, but not between excel workbooks. I'm not sure if this is a Tableau limitation or one in the jet driver it uses for connecting to excel files, my guess would be the latter. I think I heard there would be a new driver in an upcoming release, but it is possible that was just a dream. I'm sure someone else will be along any minute with more of the technical details, but I didn't want to leave you in the land that joins forgot any longer than necessary. Better to explore some other features.
That issue is something I recall from when I used to have a lot of stuff in excel. I remember it being confusing, but lately I don't even notice. I use excel to make quick test data for an idea, but most days I could do without it. Tableau rocks, you should totally buy it.
Another short term workaround is to export the sheets you're interested in as comma delimited files. In that case you just need the files in the same folder and you can join away.
You're right! I just converted them to CSV and opened them as a text file connection, worked brilliantly. It even recognized tables in the same directory that I might want to join, despite having only connecting one of them at the time. Very cool.
Excel has been kind of painful to work with. Originally I couldn't even connect xlsx files but I found via Google that it was because I had a 64-bit version of Office installed, solution being I had to apply somewhat of a patch. Link is here for that driver for anyone else that stumbles on this post: Download 2007 Office System Driver: Data Connectivity Components from Official Microsoft Download Center
Thanks for your help Noah. If anyone else figures out how to connect multiple Excel docs without converting to CSV, it'd still be good to know!