Excel: Operations Using Database or Criteria on External WS

• Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0

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" (without quotation marks).

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