Most valuable professional
  • Excel Book Excerpt

Excel Build Complex Reports Where Columns in Section 1 Don’t Line Up with Section 2

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Build Complex Reports Where Columns in Section 1 Don’t Line Up with Section 2

Problem: I need to duplicate a fairly complex form. The form has several sections. The column widths needed for the first section do not line up with the column widths needed for the other two sections.

Strategy: This is a wildly amazing and obscure solution. It has been floating around Excel Web sites for years as a novelty. However, I recently used it in a production application to produce great-looking customer statements. Here’s how it works:

1. Set up various sections of the form on individual worksheets. Make the column widths as wide as they need to be for each section of the form. In the sample, I have four different sections. The statement header has a logo and an address block that are centered on the page. The next other sections have five, three, and six columns.

2. To pull these parts together, you will build a printable statement on the worksheet that has the company header. On that page, you will paste three linked pictures that give a view of the other worksheets.

3. Select the cells for Section 1 and then press Ctrl+C to copy.

4. Go to cell A7 on the main worksheet. In Excel 2010, select Home, Paste dropdown, Paste Picture Link. In Excel 2007, use Home, Paste dropdown, As Picture, Paste Picture Link. In Excel 2003, you would have to hold down the shift key while opening the Insert menu. You could then choose Paste Picture Link.

LE10001320.jpg 

Figure 1265 Paste a linked picture of the first report section.

5. Drag this picture so that it is centered on the page.

6. Select A18:H18 and then select Home, Borders dropdown, Thick Bottom Border to draw a thick border below the pasted first section..

7. Repeat steps 3 through 6 for Sections two and three.

You can resize the pictures in the new sheet so they all have the same width, or you can simply center them on the page.

Results: You can print one unified form that does not look like it came from Excel. Fields in Section 2 are not necessarily lined up with columns in Sheet1. Note that the pictures are live pictures. If you change values on a back sheet, the picture on Sheet1 will automatically update.

LE10001321.jpg 

Figure 1266 Three pictures make up this report.

Additional Details: In our real statement application, we used a VBA macro to put together the sections. This macro can paste a different number of rows each time.

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: