Add-Innovation Home

About Add-Innovation

Contact Us

Spreadsheet Former and Printer

Very often we have lists of data held in rows, which we display as a form so that other calculations can be based on data on the same form, and for presentation reasons.

We look the data up from other sheets using Excel lookup formulae, such as the VLOOKUP function.

Usually a single cell identifies the data on the form both visually and within the calculations.
This cell can usually be selected from a list which appears elsewhere on the spreadsheet.

The spreadsheet formatting options can enhance the presentation so that it looks like a professional form, while built in Excel functions (eg. drop down boxes) also simplify selection of the target cell.

example of a vlookup, with a drop down selection box included.

It is often desirable to print not only individual copies of the form generated, but also a copy of every form possible, from the full list of values available for the target cell.

Whilst this task could be carried out manually, it is desirable to have a utility which can do this for you.

This is where the Spreadsheet Printer and Former comes in. In fact, the tool allows multiple print ranges to be specified, each being printed in turn, so that several pages of documentation can be collated automatically.

Moreover the tool also allows each range to be pasted as values, with full formatting preserved, on new spreadsheets, each named based on the target lookup range.

When the tool's icon is clicked, the following form appears

This form consists of three range selection buttons ;

  1. Definition of the "target cell" which is used by the lookup formulae.
  2. Specification of the range of cells to loop through, changing the target cell to each value in this range
  3. Selection of the range of cells to process on each cycle.
Note that many selections of ranges to process on each cycle can be specified. This enables different "forms" to be created - often on different sheets. Each would be processed on each cycle, so the "reports" would automatically be collated to be grouped together.

Ranges can be typed into each box, or the ... box clicked, and you may click and drag the mouse over the spreadsheet to define the range (even on different sheets).

Once a range has been defined, the "Add this Range" button should be clicked, whereupon it will be copied to the "Ranges to process" box. The macro will count the number of cells that are in the "loop through" range, and multiply this against the number of ranges you have selected to process, and notify you that this number of new prints or files will be generated. After that, Print or Save could be clicked, which will process every range defined in the following way.

  1. Suppose that the spreadsheet was set up as shown in the example above, with the VLOOKUP trying to find the contents of cell C2, in the range A1:L760 on sheet "Filtered", picking up column number 4, and resulting in "AVIEMORE" being displayed. The drop down box is linked to cell C2, with data available being linked to range A1:A760 on sheet "Filtered", so that the user can easily select the store number he wants. Other VLOOKUPs are positioned around the sheet, resulting in a form showing useful information about the store, and a further sheet shows general information not linked to the store, but about how to use the information. Electronically, the spreadsheet is useful as a lookup reference, but a paper copy would be useful to hand out to individual store managers, along with the instructions from the other sheet.
  2. Click on the Spreadsheet Printer and Former Icon , click on the button next to "Change Cell", and specify cell C2.
  3. Select the range too loop through by clicking on the next button down, moving to sheet "Filtered", and selecting the range A1:A760.
  4. Next, define the range to process by clicking and selecting the range with the lookups on - your form showing the store information. Click "Add this range", and select the second sheet, showing the instructions on how to use the data, adding to the list of ranges to process by clicking Add this range. The print / split form will now look like this ;
  5. Now click Print. The cell C2 will be set to the contents of cell A1 on sheet "Filtered". The VLOOKUPs will automatically update, the first range shown in box "Ranges to Process" (the information about the store now in cell C2) will be printed, then the next range shown (the general instructions on the other sheet) will be printed.
  6. Cell C2 will then be set to the contents of cell A2 on sheet "Filtered", and the process repeated. Because of the order of printing (the data sheet relating to the looked-up data, then the instructions sheet), the pages are automatically collated in the order in which they should be distributed.
    Hot Tip : If you need to send the printed sheets by post, include the name and address on the first sheet, positioned so that it would be visible through a name and address window on the envelope.
  7. Now lets say we support the paperless office idea, and don't want to waste rain forests. Instead of printing and sending the reports, we want to e-mail them, or just have them as seperate spreadsheets for reference. We don't want to send out the full, original spreadsheet, because this would be overly large, and we don't necessarily want each recipient to see other recipients details. This is where the save button comes in. In this case, the macro will save a copy of each of the selected ranges to process after each change of the lookup cell.
    The data saved does not include any formulae, but the actual values displayed. The actual range specified is copied, and pasted into a new workbook as values. The formats, and column widths are also pasted to preserve the image of the original sheet. The data can also be saved as a spreadsheet, txt file (tab seperated values), or .csv file (comma seperated values).
  8. Once the save button is clicked, a file save-as dialogue box is opened, with a default filename the same as the current open spreadsheet.
    You may change the filename and location, utilising any of the regular features of the save-as box (e.g. make directory).
  9. The filename you specify here is just a starting filename. So that each split filename can be uniquely identified, the value of the lookup cell, and the sheet name of the range being split are added in to the filename. Before the new filename is constructed, you have the option to specify where the sheet name and lookup value are added in the filename. At the same time, you can define whether you want the file saving as a standard workbook, text file, or comma-seperated file. The dialogue box that enables this is as follows ;
  10. Once OK is clicked the macro will cycle through each lookup range in the same way that it does with the print option. This time, each time a new print would be generated, the new filename is created, the data and formats copied to a new spreadsheet, and saved. The status bar at the foot of the screen will be updated to show you the process.
  11. Hot Tip 2 :If you're using Windows XP, and want to be able to link into the newly created files easily, you can add a directory listing to your windows taskbar. Just right click the taskbar, choose Toolbars, and click New Toolbar. Next, locate the directory where you've saved your files.
    Now when you click the double arrow next to the directory name, the contents listing will pop up, and clicking on any filename will open it.
  12. Hot Tip 3 :If you want to email the reports to seperate recipients (each identified by the unique identifier of the lookup cell values), then add-innovation has a tool to help you. Just email us, and we'll send you details.