Monkey Tools

Biblio Monkey

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, 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!

Working with the Biblio Monkey

What this Monkey does

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:

Power Queries
DAX Measures
Excel Formulas
VBA
Office Scripts
LAMBDA
Python

In addition to storing these items, the Biblio Monkey also allows:

Creating custom folders to organize your items
Tagging with variables for prompting upon retrieval
Direct injection of Power Queries into the workbook
Filtering to only specific item types

Getting Started with Biblio Monkey

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.

Adding New Items to Biblio Monkey

Of course, when you first load the Biblio Monkey, your library will be empty. To add a new item:

Click Add New
Select the item Type (Query, Measure, etc..)
Optional: Change the (sub)folder to store the item in
Enter the item name *
Optional: Enter a description (highly recommended)
Paste the formula/code into the large white formula area
Configure any other item Type specific options that get displayed upon choosing the type

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!

A note regarding Item names... Item names are required when creating a new entry. These act as the default names used when injecting items directly into your workbook (where supported). For this reason, duplicates are permitted in this field.

Why you should complete the Description field... While the description field is optional, we highly recommend you use it. Text from this field is displayed when mousing over items in the navigation pane as you are browsing the Biblio Monkey library. This saves you from having to select (and display) the item details to learn what it does. (Handy for security and disambiguation for similar named items!)

Retrieving Items from Biblio Monkey

Depending on the type of item you are wanting to retrieve, and your Monkey Tools license, you have some options in this area…

Injecting Items into the Workbook from within the Biblio Monkey

If your item choice and license permits it, you will see that you have two options in order inject your item into the workbook:

Right click the item and choose “Inject” from the context menu, or
Select the item, then click the “Inject” button

At this point, one of two things will happen:

If your formula contains any tags (see below), you will be prompted with a pop-up screen to map the tags to valid inputs.
If your formula does not contain tags it will be injected into the workbook for you.

Biblio Monkey provides the option to Copy item details to the clipboard

Some Biblio Monkey items can be injected directly into the workbook

Mapping the results of a tagged DAX formula back to valid inputs

Injecting Items into the Workbook from Context Menus

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.

Excel’s Cell Context (right click) menu. Simply right click any cell on the worksheet and choose to Insert From BiblioMonkey to access your stored formula and/or LAMBDA patterns.
Stay tuned as we add more context menus in future!

The "Insert From BiblioMonkey" menu at the top of the cell context menu, allowing the user to choose from stored Formula or LAMBDA patterns — Access your Formula and LAMBDA patterns directly from the top of Excel's Cell context (right-click) menu

Copying Items from Biblio Monkey

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.

Which Features Support "Inject" vs "Copy"?

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 Biblio Monkey Entries

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!

Tagging for User Prompting

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!

Creating Prompts (for Pro Users)

To create a prompt upon copying/inserting a Biblio Monkey item:

Select the text in the Formula window and right click it
Choose to Add New Prompt
Select the appropriate type of prompt you’d like to create
Enter the text you’d like to be prompted with upon copying/inserting the item in future
Click OK

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!

When selecting text to replace with a prompt... Be careful to make sure you are selecting only the text you wish to prompt. I.e. if you want to replace a textual value in a query with a prompt, don't select the quotes around the textual item, or your user will need to enter them to generate a valid query output!

Inserting a new prompt into Biblio monkey

Don't forget to save the changes to your item!

Prompting to enter Year and Division during Inject

Query results after prompting to enter Year and Division

When entering your prompt... There is a restriction on which characters may be used in the "prompt" portion of Biblio Monkey tags. Valid characters include: a-z, A-Z, 0-9, and any of the following special characters: , / . < > - + = ( )

Re-Using Prompts (for Pro Users)

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!

Tag Types and Samples

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

Manual Tagging (for Free Users)

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:

Selecting the part of the formula/code you wish to prompt the user for
Replacing it with our tag

How do you know it worked? Choose to copy the item (or inject the formula) and you should get prompted for your inputs!

When configuring tags manually... Be careful to make sure you are selecting only the text you wish to prompt. I.e. if you want to replace a textual value in a query with a prompt, don't select the quotes around the textual item, or your user will need to enter them to generate a valid formula output!

Examples of the types of available tags (and their syntax) are contained below:

General Tag Syntax

The syntax of a properly defined tag is as follows:

{p<tag>#:<prompt>}

Where:

<tag> is the lowercase 3 letter code for the type of tag you wish to use
# is a value from 1-9 indicating the number of the specific tag of this type. (This allows re-using the same tag later without prompting the user for the entry a second time.)
<prompt> is the prompt you would like to show to the user. Please keep in mind that the only acceptable characters in this area are values from 0-9, a-z, A-Z, and the -, ( and ) characters.

Tags Available for All Items

Every item supports defining prompts for both Text and Value elements. These are defined in the item’s formula as follows:

Text: {ptxt#:prompt}
Value: {pval#:prompt}

For example:

#”Filtered Rows” = Table.SelectRows(#”Grouped Rows”, each ([Year] = {pval1:Enter a value}) and ([Location] = “{ptxt1:Enter text}“))

Tags Specific to Query Items

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:

Query: {pqry#:prompt}

For example:

Source = {pqry1:Name of data source query},

Tags Specific to DAX Items

In addition to the ability to prompt for Text and Value prompts, DAX measures also support tags for the following items:

Measure Name: {pmsr#:prompt}
Table Name: {ptbl#:prompt}
Column Name: {pcol#:prompt}
Qualified Column: {pqtc#:prompt}

For example:

=TOTALMTD({pmsr1:Enter Measure name}],
{pqtc1:Enter calendar table name}
)

Tags Specific to Formulas/LAMBDA

In addition to the ability to prompt for Text and Value prompts, Formulas also support tags for the following items:

Cell Contents: {pcfx#:prompt}
Cell Reference – Fully Absolute: {pcaa#:prompt}
Cell Reference – Abs Col, Rel Row: {pcar#:prompt}
Cell Reference – Rel Col, Abs Row: {pcra#:prompt}
Cell Reference – Fully Relative: {pcrr#:prompt}
Worksheet Name: {pcws#:prompt}
Qualified Reference – Fully Absolute: {pwaa#:prompt}
Qualified Reference – Abs Col, Rel Row: {pwar#:prompt}
Qualified Reference – Rel Col, Abs Row: {pwra#:prompt}
Qualified Reference – Fully Relative: {pwrr#:prompt}

For example:

={pcaa1:Hrs Per Day}*{pcar1:Wage Rate}
*DAY({pcra1:Month End})+{pwrr1:Relative ref new sheet}

Biblio Monkey Item Management

Modifying Biblio Monkey Items

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.

Renaming Biblio Monkey Items

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:

Right click the item -> rename
Enter the new name for the item
Click OK

Re-ordering Biblio Monkey Items

To re-order Biblio Monkey items in the browser, right click the item and choose to Move Up or Move Down as desired.

NOTE: Items can only be moved up/down within the containing folder. If you need to move an item to a different folder, you'll need to reconfigure the folder first by selecting the query, changing the folder selected, and Updating the query details.

Deleting Biblio Monkey Items

To delete any item in the Biblio Monkey library you can either:

Right click the item in the Browse window -> Delete
Select the item and press the Delete button in the Manage Item(s) section

Biblio Monkey Folder Management

Biblio Monkey includes seven pre-defined “root” folders:

Queries
Measures
Formulas
VBA
Office Scripts
LAMBDA
Python

While these folders cannot be renamed or re-ordered, our Monkeys do give you the ability to create and re-order your own folders within these root folders.

Creating New Folders

To create a new folder:

Right click the appropriate “root” folder
Choose Add Folder
Provide the folder name

Moving Items to Folders

In order to move an item into a folder, you’ll need to modify your existing item as follows:

Select the item
Change the folder in the upper right corner
Click Update

NOTE:

While we wish we could tell you that we support drag and drop, the reality is that we don’t at this time. If this feature is important to you, please submit a feature request. The more requests we get, the more we can figure out how important this is to invest in.

Our measure has now been moved into our custom folder

Re-ordering Biblio Monkey Folders

To re-order Biblio Monkey folders in the browser, right click the folder and choose to Move Up or Move Down as desired.

NOTE: Folders can only be moved up/down within their root folder. If you accdidentally create a new folder in the wrong root folder, you'll need to delete it and recreate it in the correct root folder.

Renaming Biblio Monkey Folders

To rename a Biblio Monkey folder:

Right click the folder and choose Rename
Enter the new name for the folder and click OK

NOTE: Root folders cannot be renamed.

Deleting Biblio Monkey Folders

To delete a Biblio Monkey folder:

Right click the folder and choose Delete

NOTE: Deleting a folder does not delete any items within the folder. Instead, they are all moved to the root folder. If you wish to delete items within a folder, they must be be deleted individually.

Biblio Monkey Database Management

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:

For any customized location/database name (see below)
Your “Documents” folder

If we find your database, we will open it. If not, we create a shiny new one for you. Provided you are comfortable with your database being stored in your documents folder, there is really nothing you need to do other than enjoy using it. But if you want to customize things you certainly can. This can give you the ability to maintain different databases related to work vs personal, or specific to individual clients.

Biblio Monkey Database Management Screen

All database management is done via the Biblio Monkey tab of the Options Monkey form. This page contains some key information and abilities:

The location of your currently active Biblio Monkey database
The status of your database
The database version
The ability to choose an action (as described in the sections below)

NOTE: With the exception of the Location field, you probably won't ever need to worry about the Status or Version fields. This information is mainly provided for troubleshooting purposes.

Creating a New Database

The steps to create a new database are as follows:

Choose “Create new database” from the Manage Database drop down
Optional: Choose a new database name. (Note that this name must end with .accdb)
Browse for the folder you’d like to store your database in.
Click the Update Database button

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.

Helpful Tip: Right clicking the Folder button will open a windows explorer window at the database path so that you can see what other files exist there.

Choosing a Different Database

Assuming you want to change your database to a different Monkey Tools database, you will need to:

Choose “Use different database” from the Manage Database drop down
Click the Folder icon to browse for the database file you wish to use
Optional: Configure the name/path manually
Click the Update Database button

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.

NOTE: Don't forget to hit the Update Database button! Until you do, Monkey Tools will keep using the database that was already loaded!

Syncing Databases to Multiple Computers

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.

Do Sync Paths Need to be Identical Across PCs?

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.

Database Version Handling

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.

Got Feedback?

If you have any feedback or feature suggestions for the Biblio Monkey, please reach out to us!

Monkey Tools

Biblio Monkey

Article Overview

Working with the Biblio Monkey

What this Monkey does

Getting Started with Biblio Monkey

Adding New Items to Biblio Monkey

Retrieving Items from Biblio Monkey

Injecting Items into the Workbook from within the Biblio Monkey

Injecting Items into the Workbook from Context Menus

Copying Items from Biblio Monkey

Which Features Support "Inject" vs "Copy"?

Updating Biblio Monkey Entries

Tagging for User Prompting

Creating Prompts (for Pro Users)

Re-Using Prompts (for Pro Users)

Tag Types and Samples

Manual Tagging (for Free Users)

Biblio Monkey Item Management

Modifying Biblio Monkey Items

Renaming Biblio Monkey Items

Re-ordering Biblio Monkey Items

Deleting Biblio Monkey Items

Biblio Monkey Folder Management

Creating New Folders

Moving Items to Folders

Re-ordering Biblio Monkey Folders

Renaming Biblio Monkey Folders

Deleting Biblio Monkey Folders

Biblio Monkey Database Management

Biblio Monkey Database Management Screen

Creating a New Database

Choosing a Different Database

Syncing Databases to Multiple Computers

Do Sync Paths Need to be Identical Across PCs?

Database Version Handling

Got Feedback?

Share:

Related Posts

From Table or Range Monkey

Customize the Monkey Tools Tab

Biblio Monkey

Submitting Error Logs

Installation and Activation Issues