Delete Table and Create New One (Without Breaking Dependent Formulas)

[xtinct2]

Hi --

I'm using VBA...
Is it possible to delete a sheet harboring a formatted table (Table #1), then rename a new table (Table #2) to the same name that Table #1 previously had, without breaking the dependent formulas that were based off of Table #1?

Once the sheet with Table #1 is deleted, all dependent formulas display a '#REF', which is understandable. But ideally the text of the formula can remain as is, since Table #2 will now have the name of Table #1.

I'm hoping to do so without having to:
- use indirect in the dependent formulas
- temporarily rename table #1, use find/replace for the entire workbook to change the dependent formulas, rename Table #2, find/replace again, then finally delete the Sheet with Table #1

If this is possible in an efficient way it would be an immense help!

Thank you!

-kb

[rorya]

Is there a reason you can't simply copy the new data to the old table?

[xtinct2]

That's my contingency, but columns will likely be added/removed over time and i'd rather not have to write code to adjust for all that, since it could compromise integrity of the file and is only a temporary fix. Creating a new table based off of current structure is the ideal route.
Thank you

[AliGW]

Are you still using Excel 2010? If not, please update your profile.

It might be helpful to tell us WHY you need to be able to do this, as there may be a better work flow altogether for your situation.

[xtinct2]

(Updated the Excel version, thank you!)

I have a file where we have individual sheets for over 50+ clients, each with an identical table (each table is large and holds a mass amount of data).

To write dynamic formulas off all the complete data, I have a code that stacks all the tables into one master consolidation table.
Throughout the file, there are then formulas which are dependent on the master consolidation table. Whenever we update client specific data, we reconsolidate. To do so, the code creates a new consolidation, deletes the old one, then renames the new one to the name of the old one so that dependent formulas can remain consistent.

The issue is that when the code deletes the old table, all the dependent formulas break.
I tried running w/ manual calculations on, hoping that the formulas won't realize what I'm doing, but they do, and still break.

Thank you!

[AliGW]

OK - I don't understand why anything needs deleting when you consolidate, nor why thy consolidation table can't be dynamic (e.g. using formulae or PowerQuery).

Why can't your code simply update an existing table?

There are instructions at the top of the page explaining how to attach your sample workbook.

[rorya]

Totally agree with Ali - PQ would seem to be the way to go for what you describe.

[xtinct2]

Interesting --

I've never touched PowerQuery before.
I have some homework to do.

Is there a specific PQ function that would help me achieve the same aim, so that my research into PQ could be more targeted?

It's worth noting:
- clients change
- columns change
- rows change
- each client table has a vast amount of data (and are structurally identical with one another)

The aim result is to have one master table with all information on it so that I can utilize consistent formulas off of that one data source

& apologies, but due to the sensitivity of the data, I'm unable to share a sample workbook, and the current one being used is too complex to update with dummy data.

Thanks again for all your assistance!

[AliGW]

Show us a sample file and we can better advise. If you want help, you have to be prepared to put yourself out a bit - we are doing so for you for free, don’t forget!

[AliGW]

Further: a sample workbook with two desensitised employee tabs and one master tab will suffice.