Build Reports Where Columns in Each Section 1 Don’t Line Up
November 08, 2023 - by Bill Jelen
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, select Home, Paste dropdown, Paste Picture Link.
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.
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.
This article is an excerpt from Power Excel With MrExcel
