Variable Variable Names

[JenniferMurphy]

Is there a way to define a local variable (sheet1!name1) such that if I make a copy of the cells that contain both the variable and all references to it, that they will all be changed to a new name (sheet1!name2)?

I have a worksheet that contains a small variable-size table for calculating sums and averages for some experimental data. The table has about 10 columns and between 2 and 10 rows, depending on the number of readings. I have several local variables defined to make the formulas more readable such as "TimeTot", "TimeAve", "WtTotal", & "WtAve".

From time to time, I get new data. Up to now, whenever I received new data, I would replace the old data, which involved adding or deleting rows.

All that is working fine.

Now, when I get new data, I'd like to make a copy of the previous table and put the new data there so I have both the old and new data to look at. The problem is that the references in the new table point to the data in the old table. The cells in the new table that had local names in the old table have no names at all.

Is there any way to set up the table so that the variables automagically get a new unique name and all references get updated?

The only solution I have been able to come up with is to change the variable names in the table to add an index number ("TimeTot1", "TimeAve1", "WtTotal1", & "WtAve1"). Then when I copy the table, I first define new variable names with the next index number ("TimeTot2", "TimeAve2", "WtTotal2", & "WtAve2") then use Replace to change the references.

This works, but it's tedious and error-prone.

Is there a better way?