XL: Using a Path in Data Consolidation References in Excel

ID: Q75483

The information in this article applies to:

Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0, 5.0c
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for OS/2 version 3.0
Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0

SUMMARY

In Microsoft Excel, when you use the Consolidate dialog box to consolidate data, you can enter a full path to a file that contains data that you want to consolidate in the References box.

MORE INFORMATION

This is not documented as an option. Including a path is useful when you refer to cells in an unopened document located in a directory or drive other than the current path. You can save links to the source data file as usual, and if the data that you want to consolidate is contained in a worksheet file, rather than a workbook, you can use wildcard characters in the filename.

The "current path" is the path from which files will be opened when you select Open from the File menu. If you enter the current path in the References box in the Consolidate dialog box, the path will disappear when the reference is added to the All References list. To retain the path information in the All References list, choose Open from the File menu and change the current path in the File Name box before you enter the reference in the Consolidate dialog box.

Examples

In Microsoft Excel version 5.0, or in Microsoft Excel version 4.0 when the data that you want to consolidate is contained on a bound worksheet in a workbook file, if you enter the following path as your source reference in the Reference box


   c:\excel\library\[Sales.xls]Sheet1!$B$1 (for Windows)

   -or-

   Hard Drive:Excel:library:[Sales]Sheet1!$B$1 (for Macintosh)

Microsoft Excel will return the values from cell $B$1 on Sheet1 from each document located in the directory or folder noted above.

In Microsoft Excel version 3.0, or in Microsoft Excel version 4.0 when the data that you want to consolidate is contained in a worksheet file, if you enter the following path as your source reference in the Reference box


   c:\excel\library\sales*.xls!$B$1 (for Windows)

   -or-

   Hard Drive:Excel:library:sales*!$B$1 (for Macintosh)

Microsoft Excel will return the values from cell $B$1 from each document (located in the directory or folder noted above) that has SALES as the first five characters of the filename.

REFERENCES

"User's Guide 1," version 4.0, pages 380-382

"User's Guide," version 3.0, page 333

Additional query words: 4.00a 5.00c

Keywords : kbtool
Version : WINDOWS:3.0,4.0,4.0a,5.0,5.0c,7.0; MACINTOSH:3.0,4.0,5.0; os/2:3.0
Platform : MACINTOSH OS/2 WINDOWS
Issue type :