Problematic update of table from source

Topic

New Reply

I am new to Excel after many years of working with Word VBA and other fancy features. It’s fascinating. This is in Excel 2010.

We have a table that is based on a sheet that draws data from an external source. The formulas in the table relate to the rows and columns of the sheet where the data is brought in. I use relative row references and all that, i.e. if row 11, Column b of the table is =SourceSheet!$C6 then row 12 col b is =SourceSheet!$C7.

This appears to work fine at first. BUT if, when I refresh the data source and the source data has more rows than it did last time I refreshed, I lose rows.

I.e. if the last row with data of the source sheet WAS row 70 and is now Row 73, the formulas in the table change so that they drop the 3 new rows. I.e. the formula for row 70 is followed by the formula for row 74.

If I go and refresh the formulas in the first row of the table and let the change propagate down the column as the table wants, everything is fine again.

I have tried making it a regular range instead of a table (to avoid the helpful table behavior), and I have tried locking the cells. What am I missing?

I could probably write a macro that handles the change, but it would be inefficient and would slow down each refresh.

Would it help to change everything painstakingly or by macro to absolute references?

thanks,

Jessica

Reply | Quote

Replies

As so often happens I solved the problem myself. I didn’t notice the Properties for data connections, which are conveniently NOT in the Connection box. One of the properties specifies what happens when an extract pulls a different number of rows. I set it to clear things and all is well.

Reply | Quote