From Table or Range Monkey
Article Overview One of the things we have been asking the Excel team to do for a long time is give us the ability to
If you’ve worked with Excel for any length of time, chances are pretty good that you have some favourite Power Queries, DAX Measures, VBA, or formulas. And if you are like many users, you store those in a Notepad document for easy copying and pasting.
Biblio Monkey aims to change that for you, bringing you an easy-to-access bibliothèque (library) right inside Excel!
Now, while storing items is good, our Monkeys aren’t satisfied with just storing native objects… we’ve added some variables and prompting to make deployment of these items even easier. That’s right, once you meet our Biblio Monkey you’ll say bye, bye, to Notepad forever!
The purpose of the Biblio Monkey is to act as a library to store copies of key pieces of logic that you need to re-use across Excel instances. Biblio Monkey classifies these into seven major groups:
In addition to storing these items, the Biblio Monkey also allows:
Biblio Monkey will appear in the Utilities group on the far right side of your Monkey Tools ribbon tab. Click that button, and you’ll be launched into your new Biblio Monkey library.
Of course, when you first load the Biblio Monkey, your library will be empty. To add a new item:
Finally, click the Save button to Save the item to your library.
To add more items, just click the Add New button again. It is just that easy!
Depending on the type of item you are wanting to retrieve, and your Monkey Tools license, you have some options in this area…
If your item choice and license permits it, you will see that you have two options in order inject your item into the workbook:
In addition to injecting or copying items from within the Biblio Monkey interface itself, Monkey Tools also allows direct injection into the workbook in some other logical areas.
Where the item type or license does not allow automatic injection into the workbook, the “Inject” buttons will display “Copy” instead. All mapping functionality remain in place for tagged items, but the end result will be that the formula will be copied to the clipboard, allowing you to paste them in the correct location manually.
Feature/License | Free License | Pro License |
---|---|---|
Queries | Copy | Inject |
Measures | Copy | Inject |
Formulas | Inject | Inject |
VBA | Copy | Copy |
Office Scripts | Copy | Copy |
LAMBDA | Inject | Inject |
Python | Inject | Inject |
Updating a Biblio Monkey entry should be easy… and it is! Simply select the item in the browser on the left side, make the required changes on the right, and click the Update button. The changes will be written back to the library immediately!
One of the really cool features that Biblio Monkey supports is the ability to “tag” your items for prompting upon copying/insertion. We believe that this will really add some value to you.
A note to “Free” license users: The menus shown here are only available to Pro License users. You can still create tags for prompting, but you’ll need to do it manually via the instructions below… or upgrade to a Pro License!
To create a prompt upon copying/inserting a Biblio Monkey item:
The selected value will then be changed to match Biblio Monkey’s tagging structure in the formula.
Don’t forget to click Update to save the change to the Biblio Monkey database!
Each time a new prompt is added for an item, the Biblio Monkey also adds it to a menu for re-use as shown in the image to the right:
This can be extremely useful, as it allows you to configure a single prompt that gets re-used multiple times when copying/injecting the item into the workbook.
Biblio Monkey supports up to 9 tags for a single prompt type, meaning that you should have more than enough options to create as many prompts as you need.
Once again, don’t forget to click Update to save the change to the Biblio Monkey database!
Tags supported for entries vary by content type. We have a provided a full table below that lists the tag choices Biblio Monkey provides, the methods used for mapping the prompts back to valid entries, as well as sample output so you’ll know what to expect when the prompting is complete.
Applicable To | Tag Choice | Mapping Method | Sample Output | Tag Syntax |
---|---|---|---|---|
All Items | Text | Manual entry | Your text | {ptxt#:prompt} |
All Items | Value | Manual entry | 1 | {pval#:prompt} |
Query | Query | Drop down list | Transactions | {pqry#:prompt} |
DAX | Measure | Drop down list | Sales $ | {pmsr#:prompt} |
DAX | Table | Drop down list | Transactions | {ptbl#:prompt} |
DAX | Column | Drop down list | Date | {pcol#:prompt} |
DAX | Qualified Column | Drop down list | Transactions[Amount] | {pqtc#:prompt} |
Formulas * | Cell Contents | N/A | Contents of cell ignoring leading = sign | {pcfx#:prompt} |
Formulas * | Cell Reference - Fully Absolute | Pick cell from sheet | $A$1 | {pcaa#:prompt} |
Formulas * | Cell Reference - Abs Col, Rel Row | Pick cell from sheet | $A1 | {pcar#:prompt} |
Formulas * | Cell Reference - Rel Col, Abs Row | Pick cell from sheet | A$1 | {pcra#:prompt} |
Formulas * | Cell Reference - Fully Relative | Pick cell from sheet | A1 | {pcrr#:prompt} |
Formulas * | Worksheet Name | Drop down list | Sheet1 | {pcws#:prompt} |
Formulas * | Qual Reference - Fully Absolute | Pick cell from sheet | Sheet1!$A$1 | {pwaa#:prompt} |
Formulas * | Qual Reference - Abs Col, Rel Row | Pick cell from sheet | Sheet1!$A1 | {pwar#:prompt} |
Formulas * | Qual Reference - Rel Col, Abs Row | Pick cell from sheet | Sheet1!A$1 | {pwra#:prompt} |
Formulas * | Qual Reference - Fully Relative | Pick cell from sheet | Sheet1!A1 | {pwrr#:prompt} |
* Includes regular Formulas, LAMBDA and Python formulae
While we make it super easy for Pro users to apply tags to their items, these menu items are disabled for Free license users. Having said that, it doesn’t stop you from tagging the items manually, you’ll just need to write a little code.
Essentially, the process involves:
How do you know it worked? Choose to copy the item (or inject the formula) and you should get prompted for your inputs!
Examples of the types of available tags (and their syntax) are contained below:
The syntax of a properly defined tag is as follows:
{p<tag>#:<prompt>}
Where:
Every item supports defining prompts for both Text and Value elements. These are defined in the item’s formula as follows:
For example:
#”Filtered Rows” = Table.SelectRows(#”Grouped Rows”, each ([Year] = {pval1:Enter a value}) and ([Location] = “{ptxt1:Enter text}“))
In addition to the ability to prompt for Text and Value prompts, Queries also support the qry tag, which will prompt the user for the name of a query. It is defined in the following format:
For example:
Source = {pqry1:Name of data source query},
In addition to the ability to prompt for Text and Value prompts, DAX measures also support tags for the following items:
For example:
=TOTALMTD({pmsr1:Enter Measure name}],
{pqtc1:Enter calendar table name}
)
In addition to the ability to prompt for Text and Value prompts, Formulas also support tags for the following items:
For example:
={pcaa1:Hrs Per Day}*{pcar1:Wage Rate}
*DAY({pcra1:Month End})+{pwrr1:Relative ref new sheet}
To modify Biblio Monkey items, we generally recommend that you select the item in the left side navigation pane, make changes in the right side, and then click the Update button to save them.
While an item can be renamed by selecting the item, changing the name and choosing Update, it can also be renamed via a context menu as follows:
To re-order Biblio Monkey items in the browser, right click the item and choose to Move Up or Move Down as desired.
To delete any item in the Biblio Monkey library you can either:
Biblio Monkey includes seven pre-defined “root” folders:
To create a new folder:
In order to move an item into a folder, you’ll need to modify your existing item as follows:
To re-order Biblio Monkey folders in the browser, right click the folder and choose to Move Up or Move Down as desired.
To rename a Biblio Monkey folder:
To delete a Biblio Monkey folder:
As soon as you are running Monkey Tools version 1.0.8418.26518 or higher, our monkeys will check for a Biblio Monkey database when Excel is restarted. By default we check:
All database management is done via the Biblio Monkey tab of the Options Monkey form. This page contains some key information and abilities:
The steps to create a new database are as follows:
Upon successful completion, the database will be validated, the Location box will be updated to the new path, and the Status will show as “Online”. You will then have your new (empty) database available when you open the Biblio Monkey.
Assuming you want to change your database to a different Monkey Tools database, you will need to:
Upon successful completion, the database will be validated and upgraded to the latest version if necessary. The Location box will then be updated to the new path, all other details will be updated, and the desired database will be available via the Biblio Monkey.
One of the very first questions we got when demoing this feature for the first time was “how can I sync my database across multiple computers”. Our answer to that is simple: store it in a folder that syncs to other devices. (In fact, this is why we choose to set the default location to your “Documents” folder, as that is often picked up by popular syncing programs like OneDrive and DropBox.) Please note that the file does need to be synced to your PC, we do not support targeting the Monkey Tools database via a URL.
When you change the path to a database, the setting is stored in the registry of the PC. This means that the sync path doesn’t have to be the same on each device. As long as the database name is the same and Monkey Tools is pointed to the correct path for the local file location, things should work nicely.
When Excel is launched, Monkey Tools will check your database version and – if it is lower than the current release – will automatically update it to the latest version. This process also happens whenever you choose to “use a different database” as described above. In this way we ensure that you are always running the latest version of the database and can take advantage of all of the features related to it.
If you have Monkey Tools installed on multiple PCs, it is entirely possible that you could encounter a scenario where the database gets upgraded on one PC and then syncs to your other device which is running an older version of Monkey Tools. Should it happen that the database version is newer than what the currently installed version of Monkey Tools is expecting, it will ask you to update Monkey Tools to the latest version.
If you have any feedback or feature suggestions for the Biblio Monkey, please reach out to us!
Article Overview One of the things we have been asking the Excel team to do for a long time is give us the ability to
Why Customize the Monkey Tools Tab? The Monkey Tools ribbon was designed with one goal in mind – to make you more efficient when building
Article Overview If you’ve worked with Excel for any length of time, chances are pretty good that you have some favourite Power Queries, DAX Measures,
Process Overview The most likely reason you are at this page is because you have been directed here after logging a bug report, and we
Article Overview While we hope that Monkey Tools installs easily and keeps running, the reality is that software is a complicated thing, and sometimes things