Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 www.jkp-ads.com/articles/excel2007tables.asp for some pointers on working with tables.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    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

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Odd, this works fine for me. Whatever I try, the formulas get copied.
    Can you post your formulas?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •