I have been googling for a couple of days now and cannot find any resources as to how to define the scope of a table in Excel. By default a table is created with workbook scope, and I want to know if it is possible to change that scope to a specific worksheet. There are plenty of resources on how this is done for named ranges, but they don't seem to apply to tables specifically.

Another way that I could solve my problem is if it is possible to dynamically name tables within excel. This is another topic that I have googled for some time now and do not know if it is possible. It seems the table name can only be hardcoded in under the design section of the table tools ribbon. It would be great if I could set the table name via a formula so I could use a cell reference or a sheet name reference to dynamically name my tables.


To give more context I have a worksheet that has many excel tables and I want to duplicate this worksheet many times and simply change the subject of the sheet. For example build it for state X, and then copy and change it to state Y. Everything is dynamic and functioning properly, but my table names are getting appended with large numbers (tableX is becoming tableX1234567895464136549, and it only gets worse with the more copies of sheets and I need to do 30+) to keep them unique since their scope is global. This distracts from the readability of the formulas throughout the spreadsheet, which is one of the great values the tables are adding. I would rather not go through each individual sheet and manually re-name the tables as that is not a robust solution when future changes inevitably come about. If I could define the scope of the tables to be sheet specific, this solves my problem as does dynamically assigning the table names.

Thoughts?