How to Reproduce Your Excel Work Across Many Categories, Part 3 of 3

Here's how to apply an Excel report, forecast, or analysis for one product, division, or other categoy to any number of categories. Part 3 of 3.

707

Here's how we Excel users can replicate one report or analysis across many similar categories of data, like regions, products, departments, and so on.Part 1 of this series about analytical spawning with Excel described how Excel users often need to spawn one model or report across many related categories of data…across many regions, products, departments, or whatever. And they need to print the results or capture them for further analysis.

Part 2 of this series about Excel spawning showed simple layouts for each of the four worksheets needed to spawn the results: Control, Data, Model, and Results.

This final post in the series explains the macro that makes the whole thing work.

Click the button to download a free copy of the worksheets and macro discussed in this series.

The macro loops through each row of data in the database, enters the current count in the RowNum cell, then recalculates the workbook. When it does so, the model pulls the data for the current row from the database, analyzes it, and returns the results to the Results range.

Then the macro performs two optional tasks, as determined by values in the Control sheet:

  1. If the value of Capture is TRUE, the macro copies values and formats from the Results range to the Results worksheet.
  2. If the value of Print is TRUE, the macro prints the Model worksheet.

As you can see, I call the macro SpawnResults. It begins by dimensioning a number of variables that the macro will use. These variables use Hungarian notation, where “rng” indicates a range, “n” indicates an integer, “wbk” indicates a workbook, and “wks” indicates a worksheet.

The first part of the macro, where all variables are dimensioned.

The first line below freezes the screen while the program runs. When the program is done, the screen automatically unfreezes. The set statements set up various objects that the macro will use.

The macro freezes the screen and then defines the worksheet and range objects.

This line clears the prior results, if they exist. If we didn’t have the line, our new set of results could include results from a previous run.This line of the macro clears prior results.
These are three critical lines. We begin the looping process. We write the current row number to the RowNum cell in the Control sheet. And then we recalculate the workbook.

The macro starts its looping process by updating the cell where the row number is used.

Here are the two optional tasks. The first runs if the value in the Capture cell is TRUE, and the second runs if the Print range is TRUE.

Finally, the macro loops back to process the next row number.

The macro performs most of its work and then loops back for the next category.

After you set up the macro, it will be easier to run if you set up a button to launch it from the Control sheet. To do so, first activate the Control sheet, and then in New Excel, first make sure that your Developer tab is displayed.

…To display the Developer tab in Excel 2010…

  1. Click the File tab.
  2. Click Options.
  3. In the categories pane of the Excel Options dialog, click Customize Ribbon.
  4. In the list of main tabs, select Developer.
  5. Click OK to close the Options dialog box.

…To display the Developer tab in Excel 2007

  1. Click the Microsoft Office Button.
  2. Click Excel Options.
  3. In the categories pane, click Popular.
  4. Select Show Developer tab in the Ribbon.
  5. Click OK to close the Options dialog box.

The control sheet where RowNum settings is found.Then with the Developer tab visible in your Ribbon, choose Developer, Controls, Insert, Button (Form Control)

And then…

  1. Draw a button similar to the one shown here.
  2. Choose the macro name SpawnResults as the Macro Name in the Assign Macro dialog.
  3. Choose OK.
  4. The button probably will display “Button 1”. Click on the word “Button” and then press Backspace and Delete as needed to delete all text in the button. (You also should be able to select the text with your mouse and delete it, but that’s often difficult to do.)
  5. Type Spawn as shown.
  6. Click any cell to deactivate the button.

Excel spawning is a powerful capability that you can apply to many reports, analyses, forecasts, and models in Excel.

So after you have this sample version set up, make sure you play around with it. I’m sure you’ll see how powerful this analytical approach can be.