see other's post on table name in EXCEL
I was working on this exact problem today, and came up with the following solution that handles all my cases (including spaces in the worksheet names).
Where A is the worksheet name, C1 is the start of the range you want to access, and C2 is the end of the range. An actual example I used was:
[AHU CHW Clg Coils$N5:N16]
I also agree with the lack of Microsoft help on this issue. There seems to be very little easily found information regarding this on their web site.