Resetting cell references back to table names

[psgoldberg]

Situation:

• Excel data converted to Table (Insert Table); table renamed to MyTable
• Formulas created to access table data using structured table references e.g., =sum(MyTable[[#data],[Col1]])
• For specific reasons, the Table needs to be converted back to a range and then, after certain actions, converted back to a Table. Table renamed back to MyTable

At this point, the formula with structured references has been converted to cell references (no names) - =sum(Sheet1!$B$3:$B$5).

Question: How do I get the names back - i.e., change the cell references to names.

Obvious solution that doesn't seem to be available: Use the Define names| Apply Names action. However, Table names are not available.

Can this be done without writing a macro? Or using Indirect to build the formula (which of course does work).

PSG

 

[Norie]

Why are you converting the table?

 

[psgoldberg]

Why are you converting the table?

OK, you are asking me for my "specific reasons". A feature of the table automatically copies a formula down. It may be the case that I don't want the formula in all cells (I want to replace a specific formula but not all). I convert the range, put in the specific formula, and then convert back. Obviously, this is not a great practice but it does occur and there may be other reasons.

So now you know the reason, what is the solution?

 

[Andrew Poulsom]

You can enter a different formula in your Table without converting it to a Range. After entering the formula click Undo so that the copying to the other rows is cancelled.

 

[psgoldberg]

You can enter a different formula in your Table without converting it to a Range. After entering the formula click Undo so that the copying to the other rows is cancelled.

Good answer to consider. There may be other situations where converting to normal range and re-inserting a table may happen; I know that before I realized this behavior I was doing it without concern because it was very easy and there seemed to be no adverse consequences. My question remains: is there any way to re-reference, similar to apply names?

 

[Andrew Poulsom]

I don't think there is any way to do that other than re-entering the formula by pointing in the user interface.