Article ID: 211589
Article Last Modified on 10/8/2003
APPLIES TO
- Microsoft Excel 2000 Standard Edition
- Microsoft Query 2000
This article was previously published under Q211589
SYMPTOMS
When you use Microsoft Query 2000 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 2000, 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, follow these steps:
- Select a cell in the data range.
- Point to Get External Data on the Data menu, and click Data Range Properties.
- 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 the Fill down formulas in columns adjacent to data check box, 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 the Fill down formulas in columns adjacent to data check box, 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 the Fill down formulas in columns adjacent to data option is designed to fill formulas in columns only to the right of the data range.
REFERENCES
For more information about controlling how the data in an external datarange appears, click Microsoft Excel Help on the Help menu, type Control the formatting and layout of an external data range in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Additional query words: query XL2000
Keywords: kbprb kbpending KB211589
