First things first, 10 points to anyone who understands what the title is referring to (that's either Charles Atlas' workout philosophy, or one of core tenants of Bokononism in Kurt Vonnegut's standout Cat's Cradle).

Now, with my obscure reference quota filled for the day, let's assume that you've been working as an analyst for some time now. As stated in a previous post, generally you're likely to get promoted into a management position, where you no longer need your formidable skills of analysis, and they too, like all good things, will atrophy to nothingness. However, for those of you who have demonstrated such overwhelming nuance of technique that your company fears the loss of your truly awe-inspiring abilities more than they love adhering to the Peter Principle, there may indeed be another road.

This unlikely confluence of events generally begins after the fifth or sixth time that you've been asked to write a step-by-step tutorial showing how you built a specific report, only to find out that if written out in a truly step-wise fashion would take up 16 pages, and thus would never EVER be looked at. This phenomenon serves a dual purpose:

1. It misleads your company into believing that what you do is complex.
2. It is taken as evidence that you are a literal wizard, and you can't train a muggle to do magic.

Once your company has taken the bait, they may realize that you are a true elite operator, and that the loss of your abilities would result in a substantial drain on your business' intelligence. Enter the Lead Analyst (also called a head analyst, chief analyst, or executive analyst if where you work is incredibly far up it's own ass). This job title exists solely to reward those of you who have built up enough number crunching credit to make your employer realize that if they promote you to a manager, they lose, and if they don't promote you at all, you can leave at any time.

So, hotshot, what does this job involve? Simply put, your company no longer questions you about...basically anything (and if they ever do, say you're compiling, always works) and you might even be entrusted to speak directly to the notorious Dataratti that we spoke of several posts ago (they're all actually really cool guys, and appreciate being able to talk to someone who understands what their job and capabilities actually are). In exchange for these two awesome perks, all you have to be able to do is build reports that can be used by anyone...

Now all of you who work in the field and have to engage in this practice just let out an enormous sigh, and the reason for this is because now you have to write reports that:

1. Can't use macros because the end user doesn't know what a macro is.
2. Need to have built in constraints because "it just needs to be there to work" is literally begging someone to mess up a perfectly good formula.
3. Can't rely on the manual typing of actual formulae because explaining to someone that the "dollar sign isn't a typo" to someone never quite sticks.
4. Can't utilize your own macros because the end user isn't going to have access to them, and finally...
5. You can no longer say things like "pivot out", "lookup", or "index" and instead need to break each of those things down into a series of button clicks (hence why the documentation becomes 16 pages).

With that being said, let's jump into one of the easiest ways that you can build reports that allow even the unenlightened the ability to touch the sun: using a named dynamic range and a form control to auto-populate and refresh a pivot table.

Now, if any of those words are unfamiliar to you, you need to go back to the beginning of the series (with special attention to the post regarding naming ranges and active-X controls) so that you know how to do the following:

1. Build a pivot table
2. Name a range

If you've got the tricks in the bag, let's get going. In order to get this trick to work the first step is building a named dynamic range. This is a glorious trick that takes the convenience of naming a range (being able to grab that information from anywhere in a workbook) along with the UNLIMITED POWER of associating anything that someone may plug into the range with the name.

First, let's start with a sample data-set of super heroes and their secret identities, like so (you'll need developer active for this to work):

So, now's where the magic happens: click formulas-->Name Manager

In the resulting dialog box, click "new":

In the resulting dialog box, name the range whatever you want (just remember...no spaces), but the magic really happens at the bottom where it says "refers to", as shown here:

Now, in that box, you'll notice that you can put in a formula, and this one is KEY to remember both for this, and anytime you want to make a dynamic range in the future, and it is: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
* substitute the name of your worksheet for sheet1.
Essentially, OFFSET is how you set your imaginary cursor (remember, you CAN technically do everything you see in Excel from a command line, and this is how it knows where you are). It requires 3-5 variables:

• The first (the reference) is what you want your "mouse" to select before it finds it's way elsewhere.
• The second and third is the number of rows and columns you want to move in order to select your next item. You can end here, but you'd just wind up moving your "cursor" to another cell. Instead, we set this to zero for both because you don't want to move anywhere.
• The 4th is the hight (in cells) of what you're selecting (that way, you can select any number of cells vertically from where your cursor wound up) and we use "COUNTA" here to mean the number of cells that aren't blank in column A.
• The 5th and final variable is the width which serves the same function as height in the other direction. By using "COUNTA" here, we're selecting the number of columns that aren't blank in row 1.

If you did everything right, you should end up like this:

If you're thinking with portals, you may have already pieced together what we've done here: by naming the range, we can substitute any variable that'll support an array with the name of the range, AND by using that offset formula, we've just made it so that no matter what you throw into this first sheet, it gets added to the range.

This by itself is very cool, and very useful, but let's take it a step further and I'll show you how you can use this dynamic named range to make an "idiot proof" updater for a pivot table.

The first step is to of course make a pivot table, however, we're going to do something daring: a pivot table requires an array...and we've just created an array that's DYNAMIC. So, click insert, pivot table, and then when it asks you what you what you want in the table, tell it that you want to put powers in there (that's the name of my range) like so:

and boom:

BUT IT DOESN'T END THERE FOLKS. As your range is dynamic, you can add whatever rows or columns to the first page as you wish, and then if you refresh your pivot table (right click, refresh) these changes will be reflected...watch

So, now you've learned how to:

• Create a named dynamic range
• Use the offset function to set a virtual cursor (this becomes exceedingly important if ever you decide you want to code for real).
• Refresh a pivot table with information updated in the dynamic range.

Next time, we'll take it up a notch, and remove any sort of thought needed to update these tables!

It's Munchin' Time,

-Snacks