Quote of the Day
To see things in the seed, that is genius.
— Lao Tzu
Figure 1: Requirements Dissemination Process.
I currently am doing some contract work as a system engineer. In this role, I am working on generating and managing lists of requirements to drive the engineering design work. The requirements are stored in a database that I query to generate requirements lists for specific subsystems, like a receiver or transmitter assembly. These requirements are disseminated to the engineers in various subsystem documents (Figure 1).
The query outputs need some cleanup and must be augmented with information from other databases. I do this work in Excel. Figure 2 shows a simplified example of a query output as it looks in Excel initially; there actually are about a dozen columns in the database output.
Figure 2: Raw Requirement Output from the Requirements Database.
I prepare these lists for publication using Excel, tables, and structured addressing. The company style guide wants to see the requirements listed as shown in Figure 3. This style requires that the column values are not repeated if they are the same as the previous column value. I wanted to duplicate this style in my Excel workbook.
Figure 3: Requirements Formatted Per Company Style Guide For Requirement Lists.
The quickest way to format a table in this manner is to use custom formatting. The column contents are hidden by making the font color the same as the cell background; all the data is still in the table, it is just not visible. The presence of banded rows complicates the situation a bit. Figure 4 shows how I used a custom format to hide the redundant text. If the column contents are different than the previous contents, the black default font color is used.
Figure 4: Custom Formatting Dialog.
The details of the custom format formulas are shown in Figure 5.
Figure 5: Custom Format Formula Breakdown.
This table can be pasted into a Word document and it will retain the formatting from Excel.
For those who are interested, my example workbook is here.