Microsoft Office Tutorials and References

In Depth Information

Figure 16-19:
A single formula transforms the vertical data into rows.

You can easily adapt this technique to work with vertical data that contains a different number of rows. For ex-

ample, if each record contained ten rows of data, the column C header values would be 1, 11, 21, 31, and so on.

The horizontal headers would consist of values 1–10 instead of 1–3.

Notice that the formula uses an absolute reference to cell A1. That reference won't change when the formula is

copied, so all the formulas use cell A1 as the base. If the data begins in a different cell, change $A$1 to the ad-

dress of the first cell.

The formula also uses “mixed” referencing in the second argument of the OFFSET function. The C2 reference

has a dollar sign in front of C, so column C is the absolute part of the reference. In the D1 reference, the dollar

sign is before the 1, so row 1 is the absolute part of the reference. See Chapter 2 for more about absolute and

mixed references.

Filling gaps in an imported report

When you import data, you can sometimes end up with a worksheet that looks something like the one shown in

Figure 16-20. This type of report formatting is common. As you can see, an entry in column A applies to sever-

al rows of data. If you sort this type of list, the missing data messes things up, and you can no longer tell who

sold what when.

Figure 16-20:
This report contains gaps in the Sales Rep column.