Knowledge Base

XL98: Adjacent Formulas Left of Data Do Not Fill

Article ID: 192830

Article Last Modified on 9/11/2002

Microsoft Excel 98 for Macintosh

This article was previously published under Q192830

SYMPTOMS

When you use Microsoft Query 98 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" only fills down formulas that are to the right of the data that is returned by Microsoft Query. This behavior is by design.

MORE INFORMATION

In Microsoft Excel 98, 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.
On the Data menu, point to Get External Data, and click Data Range Properties.
Click to 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, if 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, if 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 Office Assistant, type "How to change the formatting of external data," click Search, and then click to view "Control the formatting and layout of an external data range." Then, click to view "Copy formulas when an external data range expands."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

179216 OFF98: How to Use the Microsoft Office Installer Program

Additional query words: query97 XL98

Keywords: kbprb KB192830