Microsoft KB Archive/77980

{|
 * width="100%"|

Excel: Using Database Information in a Two-Input Data Table
'Article ID: Q77980

Creation Date: 31-OCT-1991

Revision Date: 03-NOV-1994' The information in this article applies to:


 * Microsoft Excel for Windows, versions 3.0, 4.0
 * Microsoft Excel for OS/2, version 3.0

SUMMARY

The information below describes a procedure that will use Database Criteria as keys to summarizing database data in a two-input Data Table in Microsoft Excel.

MORE INFORMATION

This example uses EXPENSES.XLS, one of the sample files found in the EXCEL directory after you have installed Excel.

The goal of this example is to find the total expenses by category (Salary, Inventory, and Overhead) for four different creditors.

Example

Open EXPENSES.XLS and set up the Database as follows:


 * 1) Select cells A10:D256 and choose Set Database from the Data menu.
 * 2) Select cells A10:D10 and choose Copy from the Edit menu.
 * 3) Select A1 and choose Paste from the Edit menu.
 * 4) Select cells A1:D2 and choose Set Criteria from the Data menu.

Set up the Data Table as follows:  Select cells F5:K5 and enter any vendor name from the list of vendors.  Select cell E6 and type: "Overhead"  Select cell E7 and type: "Inventory"  Select cell E8 and enter: "Salary"  Select cell E5 and enter: "=DSUM(Database,&quot;Amount&quot;,Criteria)"

Create the Data Table as follows:  Select cells E5:K8. From the Data menu, choose Table.</li>  In the Row Input Cell box, type: "D2"</li>  In the Column Input Cell box, type: "B2"</li> Choose the OK button.</li></ol>

Excel will fill in the Data Table with the accrued expenses for each vendor (amounts will differ based on the vendors entered) in each of the three expense categories: Vendor1 Vendor2  Vendor3  Vendor4  Vendor5  Vendor6 Overhead $12,000       $0   $7,200       $0   $9,674       $0 Inventory     $0  $93,897       $0       $0       $0  $93,897 Salary        $0       $0       $0  $33,075       $0       $0 For information about Filling In A Two-Input Data Table in Microsoft Excel version 5.0, choose the Search button in Microsoft Excel 5.0 Help and enter the following: two-input data tables REFERENCES

&quot;User's Guide 2,&quot; version 4.0, pages 3-8. &quot;User's Guide,&quot; version 3.0, pages 265-270. &quot;Reference Guide,&quot; version 2.1, pages 153-155.
 * }

-

"THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED &quot;AS IS&quot; 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." '' ©1997 Microsoft Corporation. All rights reserved. Legal Notices.

''

KBCategory: kbusage

KBSubcategory:

Additional reference words: 3.0 3.00 4.0 4.00 5.00 two input howto