XL97: Adjacent Formulas Left of Data Do Not Fill

 Last reviewed: February 18, 1998
Article ID: Q156814
The information in this article applies to:
  • Microsoft Excel 97 for Windows
  • Microsoft Query 97 for Windows

SYMPTOMS

When you use Microsoft Query 97 to return data, and you select "Fill down formulas in columns adjacent to data", formulas may not be filled down, or the formulas may return a #REF! error.

CAUSE

This behavior occurs when the formulas are to the left of the data that is returned by Microsoft Query. "Fill down formulas in columns adjacent to data" is designed to only fill down formulas that are to the right of the data that is returned by Microsoft Query.

MORE INFORMATION

In Microsoft Excel 97, you can specify options for the data range that is returned from Microsoft Query. For example, you can select "Fill down formulas in columns adjacent to data."

Selecting the Option

To select "Fill down formulas in columns adjacent to data," use the following steps:

  1. Select a cell in the data range.

  2. Point to Get External Data on the Data menu, and click Data Range Properties.

  3. Select the "Fill down formulas in columns adjacent to data" check box, and click OK.

Behavior of Formulas in Columns to Right of the Data Range

When you select "Fill down formulas in columns adjacent to data," and you refresh or modify the data range, and the range contains additional rows, the formulas in columns to the right of the data range are copied to the new rows. If the data range contains fewer rows, the formulas in columns to the right of the unused rows are removed.

Behavior of Formulas in Columns to Left of the Data Range

When you select "Fill down formulas in columns adjacent to data," and you refresh or modify the data range, and the range contains additional rows, the formulas in columns to the left of the data range are not copied to the new rows. If the data range contains fewer rows, the formulas in columns to the left of the unused rows return a #REF! error. This behavior occurs because "Fill down formulas in columns adjacent to data" is not designed to fill formulas in columns to the left of the data range.

REFERENCES

For more information about controlling how the data in an external data range appears, click the Index tab in Microsoft Excel Help, type the following text 

     external data ranges, formatting
and then double-click the selected text to go to the "Control the formatting and layout of an external data range" topic.

Additional query words: OFF97 8.0 8.00 query97
Keywords : xlformula xlquery kbtool
Version : WINDOWS:97
Platform : WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 18, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.