ms query – fill down not resetting

Topic

New Reply

Hi,

I have a microsoft query in Excel… beside the ms query data that is downloaded I have several columns with formulas. Normally, these formulas fill up & down as the database that is downloaded changes. Every now and then (since I switched to Excel 2007) the fill up & down function is not working properly. For example, when I refresh my ms query and the data downloaded is a different size (65 rows instead of 50 rows long), the formulas stop at row 50 and do NOT drag down, EXCEPT, row 65 (the last row) would have the formulas. Rows 51 thru 64 would have no formulas in them. Any ideas on why this fill down function is NOT resetting when the query is refreshed. In Excel 2002/2003 there was a check mark I had to check to use the fill down, but I’m not seeing this in the properties anywhere… I’m wondering if it got turned off somehow???

Thanks!!
Lana

Reply | Quote

Replies

The property is indeed no longer there. However, in Excel 2007 a query is normally considered a “Table”. make sure the formulas are part of the table. If you select one of the formula cells and you do not see the table options tab on the ribbon, then the cell isn’t part of the table.

See http://www.jkp-ads.com/articles/excel2007tables.asp for some pointers on working with tables.

Reply | Quote

The formulas are still part of the table… when I click on a cell with a formula, the “Table Tools” tab DOES appear. In addition, if I pick the name of the query in the upper left hand corner of the screen (it’s call “Table_ExternalData_1”), it highlights the entire msquery as well as the cells with the formulas. In addition, the formatting of the table is also formatting the cells with the formulas. Also, the last row (row 65 in this example), has a tick mark in the bottom right hand corner. For example, the msquery is in A1:C65. Formulas should be in D2:E65, however they are only showing up in D2:E50. D51:E64 are blank, and D65:E65 actually have the formulas them. And cell E65 in particular has a tick mark in the bottom right hand corner of the cell… presumably to indicate it’s the last row & column of the table???? Is there a way to “reset” this fill down function… maybe turn it back on?
Thanks!
Lana

Reply | Quote

Odd, this works fine for me. Whatever I try, the formulas get copied.
Can you post your formulas?

Reply | Quote