Updating data table breaks formulas that refer to it

[holmes123]

(Using Excel 2007)

In my Excel workbook, I have a number of formulas that reference a data table called "tblData." The data table is in the same workbook on a worksheet named, "RawData."

Every month or so I need to replace the contents of tblData with an updated version of the data. The updated version gets outputted to a separate Excel workbook. What's the best way to update tblData with the latest version of the data? If I try to paste the updated worksheet into my workbook with the formulas and convert it to a data table, I can't name it "tblData" because that table name is already in use (i.e., the outdated data on the RawData worksheet). If I delete the outdated data or the RawData worksheet, I delete the table name, and the formulas loose the reference (lots of #ref). The formulas remain broken even when I convert the updated data to a data table and name it tblData.

Any suggestions? I assume I'm missing something simple.

Thanks.

[lmp]

Hello,

change the definition of the name to use a formula that does not get upset if data is overwritten/added/deleted.

For example, the name MyData with this formula ...

=INDEX(Sheet1!$A:$A,1):INDEX(Sheet1!$Z:$Z,COUNTA(Sheet1!$A:$A))

... will always refer to all the data in Sheet1, columns A to Z, starting in cell A1, down to the last row of data in column A. You can delete and copy and paste data into Sheet1 to your heart's content. The range name will always capture it.

[holmes123]

Hello,

change the definition of the name to use a formula that does not get upset if data is overwritten/added/deleted.

For example, the name MyData with this formula ...

=INDEX(Sheet1!$A:$A,1):INDEX(Sheet1!$Z:$Z,COUNTA(Sheet1!$A:$A))

... will always refer to all the data in Sheet1, columns A to Z, starting in cell A1, down to the last row of data in column A. You can delete and copy and paste data into Sheet1 to your heart's content. The range name will always capture it.

This is a very large dashboard and there are many formulas that refer to the table name. Many use structured references (e.g., =DeptSales[[SalesPers]:[Region]], =DeptSales[[#Headers],[ComPct]]), which is one of the nice advantages of using a data table. Changing the formulas to not use the table name (and therefore structured references) seems a step backwards.

Also, with the data table, I can currently delete, copy, and paste the updated data into the sheet and things will work. But I was hoping for an approach that's more streamlined and easier to automate, like simply swapping out the entire sheet with the updated one. Perhaps that's not possible.

[James@tangerine]

Hi,

Did this ever get resolved? I'm having the same issue with re-setting the tables I have linked to SharePoint...I need to recreate the SP links and so delete/clear the existing table before I do this.