Excel 2016 - I have an Excel file that imports a table from SQL Server via a connection created with the Data->Get Data->From Database->From Sql Server Database.
When I built the file, I imported the latest information from the SQL table, which created a table automatically in Excel, and then went to work creating all the formulas I needed to get the summary information I needed.
All worked well - then I did a second import from the same connection with a new date range, a smaller one, so fewer rows were imported.
The issue is that, upon import, it appears that rather than clearing the data from the first import and then creating the new table, it actually deletes the rows that are no longer being used. This in turn causes a slew of REF errors and re-shuffling of ranges within all my formulas.
Does anyone know a way to work around this? I'll give two examples:
1. In all of my formulas, I reference the pertinent table range as A2:A750000. 750,000 rows is the max rows we ever expect. I don't use the range A:A for two reasons: I don't want A1 included in the range, and doing so greatly impacts performance. So when the new data is imported, that A2:A750000 range gets jacked up in all the formulas that contain it.
2. The more critical example - I have an IF formula that looks at each cell in column A in the table and returns a number depending on the condition. The aforementioned new import of data throws REF errors for all rows that were populated before, and then are no longer populated with the new refresh. Again, because it appears the new import deletes rows rather than clearing data.
Any help with this would be greatly appreciated.
When I built the file, I imported the latest information from the SQL table, which created a table automatically in Excel, and then went to work creating all the formulas I needed to get the summary information I needed.
All worked well - then I did a second import from the same connection with a new date range, a smaller one, so fewer rows were imported.
The issue is that, upon import, it appears that rather than clearing the data from the first import and then creating the new table, it actually deletes the rows that are no longer being used. This in turn causes a slew of REF errors and re-shuffling of ranges within all my formulas.
Does anyone know a way to work around this? I'll give two examples:
1. In all of my formulas, I reference the pertinent table range as A2:A750000. 750,000 rows is the max rows we ever expect. I don't use the range A:A for two reasons: I don't want A1 included in the range, and doing so greatly impacts performance. So when the new data is imported, that A2:A750000 range gets jacked up in all the formulas that contain it.
2. The more critical example - I have an IF formula that looks at each cell in column A in the table and returns a number depending on the condition. The aforementioned new import of data throws REF errors for all rows that were populated before, and then are no longer populated with the new refresh. Again, because it appears the new import deletes rows rather than clearing data.
Any help with this would be greatly appreciated.
