Excel: Operations Using Database or Criteria on External WS

ID: Q58226


The information in this article applies to:


SUMMARY

When using Microsoft Excel, you can perform database operations using a database or criteria range from another worksheet or macro sheet. To do this, choose Define Name from the Formula menu to define the database or criteria as an external reference to the worksheet or macro sheet containing the database or criteria range.


MORE INFORMATION

For example, if a database is set on Worksheet1, and you want to do an extract to Worksheet2, do the following:

  1. With Worksheet2 active, choose Define Name from the Formula menu.


  2. In the Name box, enter the word Database.


  3. In the Refers To box, enter the worksheet name followed by an exclamation point and the database range. Be sure to precede the worksheet name with an equal sign. For example, you could enter the following:

    =Worksheet1!$A$1:$G$150

    Alternatively, if the database is already defined on Worksheet1, you could enter the following as your reference on Worksheet2:

    =Worksheet1!Database


  4. You can define the criteria on Worksheet2 in the normal manner, or you can use a criteria already defined on Worksheet1 by following the above steps to define the name "Criteria" on Worksheet2.



REFERENCES

"Microsoft Excel User's Guide 2," version 4.0, pages 305-357

"Microsoft Excel User's Guide," version 3.0, pages 347-387

"Microsoft Excel Reference," version 2.2, pages 148-157

Additional query words: macrosheet 2.20 3.0 4.00


Keywords          : 
Version           : 
Platform          : 
Issue type        : 

Last Reviewed: March 15, 1999