I have used external data queries for several years, connecting to a variety of data sources, but most frequently to SQL server 2005 using MS Query and ODBC. Anyway, in Excel 2007, when I refresh my queries and the data range adds new rows to the query table, the formluas do not fill down past the previous extent of the data range. These are not external queries converted from a previous version of Excel; I built them in Excel 2007, and the issue persists across multiple workbooks. To answer the inevitble suggestions:

1) The formulas are definitely in the query tables, not adjacent.

2) "Extend data range formats and formulas" is enabled in the advanced tab of Excel options.

 

I've found threads on other sites where users had similar problems, but have not seen a satisfactory solution or even a good response to their inquiries. Any ideas?

 

P. Dolan

Stat Analyst, TN Dept of Safety

• Changed type Max MengMicrosoft contingent staff, Moderator Tuesday, June 21, 2011 6:59 AM

Hi Dolan,

I'm try to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.

As you probably already know - starting in 2007 all external data ranges that are creatd by using the user interface are created as Excel tables.  They should automatically expand when new rows are added.

You mention that the formulas are definitely in the tables...when you import the new data can you see if Excel is considering these new rows as part of the table?  I'm assuming they are, but want to rule that in or out first.

Also - in the column that has the formulas...do ALL of the cells in that column have a formula before the import?  If one or more of them are blank it may be confusing Excel when the new rows are added.

We are changing the issue type to "Comment" because you have not followed up with the necessary information. If you have more time to look at the issue and provide more information, please feel free to change the issue type back to "Question" by editing your initial post and changing the radio button at the top of the post editor window. If the issue is resolved, we will appreciate it if you can share the solution so that the answer can be found and used by other community members having similar questions.

 

Thank you!

I have the exact same problem.  Using Excel 2007, from Microsoft Query I am accessing a SQL database.  If I change my query and the number of rows is greater than the previous query then the data from the SQL database fills down properly however my adjacent formula cells which are in the table (and shown as automatically formatted as part of the table) are blank from the last lowest row to the new bottom row.  Interestingly, the formula manages to copy to the bottom row of the new range, so the table is "filling" down the formula but it is missing everything in between.

In Excel 2007 if I create a new Excel 97-2003 workbook and recreate the exact same query it manages to fill down the formula properly.  I just happened to find this out now but I'd rather not step back a version given other incompatibilities.

I have the same issue with excel 2010.  To answer Ryan's last question,  Excel does seem to consider all the added rows as begin part of the table.   On the pre 2007 queries, there was an option under 'External data properties' to specify whether you wanted the formulas to copy down to the new rows. When this option was selected, it worked without fail.  Under 2007 and 2010 this option no longer appears, but most of the time the formulas do copy down properly, but eventually something happens to cause this to stop occuring.  The only fix I have found is to recreatet the whole query again.  It then works for awhile and then stops working at some point. I wish I could isolate what causes it to stop, but I haven't been able to figure it out.

I have the same issue with excel 2010.  To answer Ryan's last question,  Excel does seem to consider all the added rows as begin part of the table.   On the pre 2007 queries, there was an option under 'External data properties' to specify whether you wanted the formulas to copy down to the new rows. When this option was selected, it worked without fail.  Under 2007 and 2010 this option no longer appears, but most of the time the formulas do copy down properly, but eventually something happens to cause this to stop occuring.  The only fix I have found is to recreatet the whole query again.  It then works for awhile and then stops working at some point. I wish I could isolate what causes it to stop, but I haven't been able to figure it out.

Same with me and Excel 2010.  The help refers to the option, but my dialog box does not have it.  (Another option that has disappeared is the ability to get column headers derived from the returned query.)

Excerpt from help:

 

1. On the Data tab, in the Connections group, click Properties.

1. In the External Data Range Properties dialog box, select the Fill down formulas in columns adjacent to data check box.  

 

• Edited by John D Marino Wednesday, September 07, 2011 12:58 AM

I am having the exact same issue with both Excel 2007 and 2010.  Never had this issue with earlier versions.  I can get it to work (for awhile) by deleting the columns with the formulas and recreated them.  Eventually it stops working again.  This is very annoying and makes some of our automated queries very unreliable.   Hoping to see a fix for this soon.

Same with me and Excel 2010.  The help refers to the option, but my dialog box does not have it.  (Another option that has disappeared is the ability to get column headers derived from the returned query.)

I believe I was looking at the same help file you were.  It says at the beginning of the article:

...applies only to an external data range that is converted from a Microsoft Office 2003 program...

The article goes on to explain that the new [listbox-based] tables will automatically fill the new formulas. Funny, but I have never had it fill the formula down to additional new rows. As others have commented, the very last line does get the formula, but every row in between that exceeds the original number of rows is blank.

I have found an answer for what was happening to me:

It is initially caused when the Data Range Property of "Preserve column sort/filter/layout" is turned off.  After that, any time the table query is refreshed and it returns more rows that were originally there, all of the additional rows (except sometimes the last row) do not have the formula.  Filling down the formula corrects it one time, but if it's refreshed again, the formula is gone again. 

I have only been able to resolve it by turning on "Preserve column sort/filter/layout",  deleting the entire worksheet column, then recreating the column.

• Edited by data digger Wednesday, January 25, 2012 8:14 PM

For myself, the new rows were indeed part of the table.  All of the cells above the new rows have the formula.  I have tried filling down the formula from the top cell to the very bottom, but after refreshing the table's query the formula dissapears from the very same cells.

I have found an answer for what was happening to me:

It is initially caused when the Data Range Property of "Preserve column sort/filter/layout" is turned off.  After that, any time the table query is refreshed and it returns more rows that were originally there, all of the additional rows (except sometimes the last row) do not have the formula.  Filling down the formula corrects it one time, but if it's refreshed again, the formula is gone again. 

I have only been able to resolve it by turning on "Preserve column sort/filter/layout",  deleting the entire worksheet column, then recreating the column.

I've confirmed this behavior as reported by data digger.  At least it's nice to now know what is triggering the issue, so I can try to avoid it. I would hope that Microsoft will fix it at some point.

WOW! Finnally this solve my update problem. Same as the one describes here.

Did Delete and rewrite all columns formulas and check "Preserve column sort/filter/layout" on all my data connections.

Wish I found this thread before.

Thx

This worked for me too. Thank you

Did Delete and rewrite all columns formulas and check "Preserve column sort/filter/layout" on all my data connections.

You dont have to delete your columns, just delete all content except for the first line and fill down again.

Did Delete and rewrite all columns formulas and check "Preserve column sort/filter/layout" on all my data connections.

You dont have to delete your columns, just delete all content except for the first line and fill down again.

I initially didn't know what you meant by fill down again since there is only one row of data but double clicking into each of the formulas and pressing enter did the trick for me.  I guess that's the same as filling down even though there is no other row to fill down.

Did Delete and rewrite all columns formulas and check "Preserve column sort/filter/layout" on all my data connections.

You dont have to delete your columns, just delete all content except for the first line and fill down again.

Excellent suggestion.  This works.