0

I have an Excel workbook containing an "old" table which is referenced all over the place in thousands of formulas. I want to replace this "old" table with a "new", already exisiting table. All the formulas should therefore reference this "new" table. I'm now desparetly searching for a way to update the reference/name of the "old" table to just point on the "new" table range.

For everyone not very familiar with this topic. If i rename the old table every formula updates the reference to this new name of the old table. Maybe there's also a way of stopping the formulas from updating that reference?

I've tried Search&Replace but i always get hundreds of erorrs, because excel says the formulas are invalid and it's very tedious. Deleting the old table gives me a REFERENCE! error. I've tried to change the range of the old Table with vba or overwrite the names but nothing works.

baby blizzard
  • 1

2 Answers2

1

If the old_table has been given a name and that name is used in all the formulae then just edit the name.

If not and the old_table is referenced by rows and columns A1:G50, plus the sheet name then I would do the following:

  1. on the sheet where the old_table is referenced I would edit/replace the "=" for the cells that reference that table. I use "xyxy" as it is sufficiently "odd'

  2. do an edit/replace to replace the A1:G50 with the new table address: eg B64:P90 in all the cells relevant.

  3. do an edit/replace of "xyxy" with "=" to make the formulae live again.

Done this lots of times but tend to name things now as it makes these changes easier...

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
0

My advice would be to simply delete everything from the old table except the headers and the first row, copy the new table and paste it in the old table, overwriting the first row. Excel should automatically expand the table to fit the new data.

If there are different number of columns in the new table, add / remove these columns from the old table before pasting in the new data.

andrewb
  • 1,129
  • 5
  • 9
  • my "new" table is from powerquery so i need a way to attach the name/reference of the "old" table to the whole "new" table, but thank you for the response – baby blizzard Apr 18 '23 at 12:07