Regular readers will recognize the name Nick Osdale-Popa from his contribution to the site a few months ago that gave all of us a better color management tool for Excel.

Well he’s been at it again, only his activity wasn’t recent; it was almost four years ago!

Nonetheless, I only discovered his idea this week and thought that I must pass it along to you.

You’re well aware that Monarch exports to Excel files and many other file formats, and I’d bet that you often just copy from the Monarch table window to Excel, without even exporting to a file.

One of my tried and true techniques is to build Excel templates that allow me to paste (or otherwise import) fresh data from Monarch into Excel sheets that already have formulas built to perform special calculations on that data that I just can’t do with Monarch.

Of course you have to be very careful to not accidentally overwrite and lose the Excel formulas when bringing the Monarch data into the sheet.

Well, Nick’s idea takes advantage of the copy and paste operation. It’s so simple, yet immensely useful and powerful.

He simply writes the Excel formula into a character type calculated field, enclosed in quotes, and includes a leading equals sign.

The wonderful part is that when you paste that text from Monarch into Excel, Excel treats it as if you’d just typed the formula and evaluates it as it would normally.

This opens up a whole bunch of possibilities for us.

I have some workbooks that I’ve programmed to update the values from Monarch automatically, and I’ve written a fair bit of program code to handle adding the correct additional formulas to those values and to place those formulas in the proper rows. Using the Excel formulas built into the Monarch model, I won’t need to do that.

As long as the calculated field with Monarch is built carefully, Monarch values can be integrated into the Excel formula, such as this:

"=HYPERLINK("+Chr(34)+URL+Chr(34)+")"

Chr(34) gives us a double quote. URL is the field name in Monarch.

Even the row references can be correct by using Monarch’s Rowno() function to get the row numbers in your Excel formula, like this:

"=F"+Trim(Str(Rowno()))+"*0.1"

The Rowno() function is a good choice, by the way, because it ignores any filtering that might be active, and always returns the current row number in the table, as opposed to the Recno() function which always returns the exact record number in the data source and then shows gaps if the records are filtered.

Thanks Nick!

By being creative and building Excel formulas carefully in Monarch, you’ll save a lot of time and effort in producing your final Excel product as you excel with Monarch.