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.