XL: Using a Path in Data Consolidation References in Excel

 Last reviewed: February 2, 1998
Article 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: 3.00 4.00 4.00a 5.00 5.00c 7.00
Keywords : kbtool kbfasttip
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

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 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.