I’ve touched on some of the aspects of the current economic conditions here over the last few months and I’ve made some suggestions, however small, about how organizations can overcome some of their challenges.But today it gets significantly more personal as I learned today that a friend has been given notice that his job is about to end.

Nick Buol is currently an IT Manager in Iowa, and he needs your help to find alternate employment to support his young family in today’s challenging times.

As much as I wish it were different, I can’t do much for him personally. I thought about it at length and decided that the one thing that I can do is share his situation with you here. Since ExcelWithMonarch.com readers come from all industries and all sizes of businesses, I hope that one of you can help put him in touch with a good company that needs his services, maybe even at your company.

Enough from me; let’s hear from Nick:

“Hello world. I have been fortunate enough to know someone like Sandy who is willing to do something to help me out. You see, I was just informed earlier today that due to cost cutting at my employer, I have just a few weeks to find another job with my company, or I will be under “critical examination” which in the past has meant job loss for others in this situation. I know that times are tough for everyone, but I think that I have a lot to offer a company looking for a successful IT manager.

Without boring everyone with the details, let me just say that I have had the backing of several executives of the international Fortune 500 company that I work for. Unfortunately, in the current economy, that isn’t always enough. Several have tried to go as far as creating an entire new job role to keep me with the company, but the top executives are sticking to their “no new jobs” stance for the next 3-4 months. So if you know of anyone looking for a proven IT or support operations manager, please consider downloading my résumé and passing it on. Thank you!”

The Christmas Gift

I honestly hope that this is the one and only time that I ask for your help for situations like this. I do my best to keep this site on topic, but this is all I can do for Nick, and I’ve been in his shoes.

There are plenty of ways to find work with job sites and such, but I find the best results always come not from listings, but from people.

My meager gift to Nick is my ability to share his story with you. Please continue the gift and download his résumé now. Pass it on to someone you know. That person might be able to help out a total stranger. Act on it today. Now. Let’s give Nick and his family a nice gift this Christmas: your time. Just a minute or two is all you’ll need. There’s no cost to you.

Thanks. I really appreciate your help.

Everybody that uses Excel regularly has their favorite functions that seem to appear in most of their work, and I’m no exception. Working as I do with data extracted by Monarch, I use Excel’s various lookup functions frequently.

That said, I rarely use the Indirect function. I just never really thought that I needed to take advantage of it.

Quoting from Excel’s online help, you “Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself”. The little example that is supplied in the help certainly didn’t do much to convince me that I need to include Indirect in my list of regularly used functions.

But that opinion has changed.

The old way is too slow

I recently found myself working with an Excel file that had dozens of sheets, each of which had the same structure but reflected data for different business lines. Within each sheet there were column groups for actual results, budgeted amounts, and forecasted amounts.

I wanted to make it easy to graph the various amounts for different topics and metrics for each business line by grouping the values to facilitate developing the graphs. Since I had quite a number of graphs to develop for each of the worksheets, I decided it was best to create a sheet for each business line that summarized all of the graphing values for that business line.

I’ve done similar work previously by building the first sheet using various lookup functions to retrieve values from other sheets, then duplicating the master sheet and, using the find and replace dialog, replacing references to the worksheet names first used with other names. This can be a tedious and time consuming job that requires some care.

Develop off-sheet references quickly and correctly

Instead, this time I used the Indirect function to handle the changing sheet names for me and I couldn’t believe how quick and easy the process of developing the values for my graphs became. I was honestly shocked.

I began the work by making it easy to build all of the range references I would need by separating the components. One cell held the name of the worksheet from which I wanted to retrieve the necessary values. Then three cells would contain the range to be used within that worksheet for each group of actual results, budget values, and forecast values.

That’s really all of the information that the Indirect function needs, but there’s one important if not critical point to discuss regarding the Indirect function.

Odds are that you’ve been using Excel for awhile, and are familiar with how Excel builds off-sheet references. For instance, if you need cell A5 on Sheet1 to be ten percent of the value in A5 on Sheet2, the formula you get is:

Sheet2!A5*0.1

If Sheet2’s name is “Budget Data” however, what you get is:

‘Budget Data'!A5*0.1

This distinction is important if we want our Indirect function to work reliably. Sheet names containing spaces must be enclosed with single quote marks, whereas sheet names without spaces must not include quote marks.

With the sheet name keyed into B1, the budget values range listed in B2 as $AQ$16:$BH500, the actual values range listed in B3 as $E$16:$V$500, and the forecast values range listed in B4 as $X$17:$AO$500, I set the formula for C2 as

=IF(ISERROR(FIND(" ",$B$1)),$B$1&"!"&B2,"'"&$B$1&"'!"&B2)

C3 becomes

=IF(ISERROR(FIND(" ",$B$1)),$B$1&"!"&B3,"'"&$B$1&"'!"&B3)

and C4 becomes

=IF(ISERROR(FIND(" ",$B$1)),$B$1&"!"&B4,"'"&$B$1&"'!"&B4)

Finally, I included the Indirect function in a VLOOKUP formula similar to

=VLOOKUP(KeyValue,INDIRECT($C$2),ColumnOffset,FALSE)

Combine the INDIRECT function with Monarch v10 exports

One of the great new features in available in Monarch v10 is its new ability to export Summary in a different manner. Not a different file format, but a new level control has been introduced with which you can export data.

Experienced Monarch users will know that Monarch’s summaries offer the choice to group, and provide subtotals for, related data. Monarch v10 now allows you to export these groups individually. Well, not quite individually, per se, as you must export the entire summary, but where the exported data for each group goes is up to you.

You have two available options:

• 1) You can create individual files for each group, or
• 2) You can create different tables for each group within a single file.

What does this really mean? Let’s say that you’ve got data summarized for 26 products listed: Product A through Product Z, and you want to export to the Excel xls file format.

Option 1 will give you 26 files named “Product A.xls” through “Product Z.xls”.

Option 2 will give you 26 sheets within a single file, with a name of your choice, each sheet named “Product A” through “Product Z”.

You can export to any of the following file formats: accdb, db, dbf, htm, html, mdb, pdf, txt, wk3 (Seriously, who’s still using that? ), xls, xlsm, and xlsx.

I tested this new exporting technique with the model I discussed recently concerning using Monarch for organizing folder and file names. In about five minutes Monarch created almost 350 xls files for me.

Let’s think about this for a moment. Do you need to share data electronically, but not everything should go to everyone? Do you often customize your data packages for your audience? How long does that take you?

Remember as well that these aren’t raw data dumps from the Table window. These are fully formatted custom summary views, complete with Excel’s outlining enabled and all the other bells and whistles that Monarch summaries offer, if that’s the format you use for exporting.

Your ROI for upgrading to Monarch v10 might just be met within a couple of hours by using this one single feature alone.

Just try doing that work without Monarch. Instead, do it purely with Excel. Oh, and I’ll be generous and even give you a whole ten minutes. Start now. I double-dog dare you.

Nothing handles data like Excel paired with Monarch

Once you master Excel’s Indirect function I’m confident that you’ll envision all manner of opportunities to capitalize on Monarch v10’s new exporting abilities. Finally, here’s a bonus for you for reading this far: an Indirect function demo file (25kb).

Upgrade to Monarch v10 and now more than ever, you’ll excel with Monarch.

Wow, that was fun. I haven’t the chance to do that for a little while.

The other day I met and spent a couple of hours with a very nice fellow who’s been around the IT world for some time. Lately he’s been working with ASP, Access, Dot Net, SQL Server, that sort of thing. He was no stranger to various ERP applications and the endless and varying needs of users.

He’d been told stories of my reporting systems and was curious as to how I accomplished the work, especially the turnaround time.

I started the show and tell session with a run through of the end result: Excel reports that he hadn’t yet seen. I discussed with him the various inputs, the size and the number of pages in the source reports, and that was he was seeing was only one in a series of distribution reports.

Clearly he saw how this file would be an asset for managing the business. That’s when it got really interesting.

“How long does it take to produce and summarize all of this?” he asked, seemingly expecting an answer in a number of days.

“About ten to 15 minutes per file, once you’ve got your new weekly ERP reports ready to go”, I answered.

I wish I had a picture of his face for you.

Shock and Awe

“How do you go from text files to Excel data? That’s the key isn’t it?”

The poor man just wasn’t ready for what was coming.

Seeing that we had the available time, I launched into full demo mode. I ran through building extraction templates. I built both simple and more complex formula based calculated fields. We even made a custom function, and I built both internal and external lookup fields.

I showed him some ideas for user-edited fields, and runtime-parameter fields. We utilized address blocks, filters (simple and compound) and custom sorts. All the while, we talked about the technical specs, any current limitations, and the array of file types that can be used for inputs and outputs.

We concluded the full Monarch demo by whipping up a handful of summaries to show the difference between a static text file and the versatile, analysis-ready results that are generated with just a few minutes of work in Monarch. I pointed out that our efforts - though they took very little time to generate anyway - need never be duplicated as the model can be reused as a processing machine thousands of times, by simply applying the saved model to new data.

“How quickly can you write SQL queries for all of those ad-hoc data requests you get?” I asked, as this topic came up during the demo.

“Fairly quickly, but not that quickly, and nowhere near that easily”, was his response, sounding a tad disappointed while slowly shaking his head.

I closed with a brief line by line examination of one of my Monarch automation processing routines, done with VBA.

“So you’re saying that for repetitive data tasks you never do the work manually? Your program just applies the models and those project things to the new weekly reports? What do you need to do to complete the process?” he inquired.

“Right. Once the models, projects and a little programming is done, this system is utterly hands-free. What I do to finish it off? I go make myself a coffee. The VBA program even combines the related data and formats my final distribution reports.

Enter a New Phase with Monarch

I don’t think that I’ve ever seen someone so eager to stand up that very instant, return to his office and begin to excel with Monarch.