Defining the Scope of an Excel Table or Dynamically Assigning a Table Name

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?

I am renaming them with something meaningful.

Even finding out that these things are not possible would be very beneficial so I can start moving forward with some alternate solutions. Thanks for the consideration!

Yeah, that is what I was afraid of. I was hoping to avoid manually renaming the roughly 900 tables (about 30 on the template sheet and then roughly 30 copies) and the number of sheets is not static so in the future more sheets will be added and then with the add comes the manual renaming step again. I really don't understand why you can't set table names via a formula. That is what I was hoping to do and just slap a meaningful prefix on the table name that is defined by the sheet name. Oh well, thanks for the feedback!

My VBA is weak at best, any chance you could provide a generic example?

I was able to write some VBA to do the renaming in an automated fashion, so thank you for the suggestion!