Keep formula references when table deleted and replaced

[rob51852]

Hi,

I’ve got a table called DataTable and various formulas that reference the table using the table and column names.

When the table is deleted and replaced all the formula references mess up leaving me with #REF errors.

Is there an equivalent to the $ for table references to stop this happening?

Cheers

 

[CountTepes]

By deleting a table, are you deleting the sheet, or the rows of data, or the cells?

Bare in mind that once you delete any of those, any formulae which reference that data, will lose their reference to the cells, rows, sheet or whatever, as they cease to exist. They are ex-cells. They are no more.
Therefore the references point to nothing, which gives the #REF error.

If you re-write your formulae using VBA programming, then for example If you reference cell B15 on a sheet using Cells(15,2) or Range("B15") then B points to the spreadsheet at cell(15,2), regardless if you deleted that cell and it was replaced by another.

The reason for that is that references in Excel are fixed to a pointer to a location and the pointer once removed cannot be automatically replaced. Whereas VBA calculates the Cell location, when it runs.

 

[rob51852]

Thanks Count. I’ll try that out.