Microsoft Office Tutorials and References
In Depth Information
FIguRE 4-6 Product and ProductModel in the sales order detail table.
This Excel table seems to be very near to the final result; she is just missing the date of the order
to have a table containing all the data she needs. Michelle now recognizes how valuable
VLOOKUP is in this situation: if the date of the order is not in the sales order detail table, it
might be in some other table, and she can use VLOOKUP again to put it into the detail table.
It happens that the table SalesOrderHeader contains an OrderDate column, and that table
seems to share the column SalesOrderID with the SalesOrderDetail table. It is just a matter
of loading the SalesOrderHeader table into Excel, adding one more VLOOKUP column to the
sales table and she ends up with the final result, which contains order date, product, product
model, quantity, and line total. She needs to slice data for sales year and month, so she adds
two new calculated columns to the main table (YEAR and MONTH of the OrderDate) and can
now use the PivotTable to produce the required report. In Figure 4-7, you can see the final
worksheet, which is the source of the pivot table.
The final Excel workbook file is more or less 16 MB in size. It is pretty slow whenever Michelle
wants to update something, yet it works and solves the problem. Without knowledge of data
modeling, Michelle has developed a method of performing analysis: using VLOOKUP and
loading tables is the way to get reports done.