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:

  1. Click Add New
  2. Select the item Type (Query, Measure, etc..)
  3. Optional: Change the  (sub)folder to store the item in
  4. Enter the item name *
  5. Optional: Enter a description (highly recommended)
  6. Paste the formula/code into the large white formula area
  7. 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!

An empty Biblio Monkey library
An empty Biblio Monkey library
Adding your first Biblio Monkey item
Adding your first Biblio Monkey item
Showing a specific Biblio Monkey item
Showing a specific Biblio Monkey item

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:

  1. Right click the item and choose “Inject” from the context menu, or
  2. 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
Biblio Monkey provides the option to Copy item details to the clipboard
Some Biblio Monkey items can be injected directly into the workbook
Some Biblio Monkey items can be injected directly into the workbook
Mapping the results of a tagged DAX formula back to valid inputs
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.

Creating Prompts

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

  1. Select the text in the Formula window and right click it
  2. Choose to Add New Prompt
  3. Select the appropriate type of prompt you’d like to create
  4. Enter the text you’d like to be prompted with upon copying/inserting the item in future
  5. 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!

Inserting a new prompt into Biblio monkey
Inserting a new prompt into Biblio monkey
Don't forget to save the changes to your item!
Don't forget to save the changes to your item!
Prompting to enter Year and Division during Inject
Prompting to enter Year and Division during Inject
Query results after prompting to enter Year and Division
Query results after prompting to enter Year and Division

Re-Using Prompts

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}
Query Excel Table Name Drop down list Transactions {pxtn#:prompt}
Query Excel Named Range or Array Drop down list rngTransactions {pxnr#:prompt}
DAX Measure Drop down list Sales $ {pmsr#:prompt}
DAX Table - Any Drop down list Transactions {ptbl#:prompt}
DAX Table - Fact Tables Only Drop down list Transactions {ptfc#:prompt}
DAX Table - Dimension Tables Only Drop down list Customers {ptdi#:prompt}
DAX Table - Calendar Tables Only Drop down list Calendar {ptca#:prompt}
DAX Table - Disconnected Tables Only Drop down list Measures {ptds#:prompt}
DAX Table - Measure Tables Only Drop down list Measures {ptdm#:prompt}
DAX Column Drop down list Date {pcol#:prompt}
DAX Qualified Column Drop down list Transactions[Amount] {pqtc#:prompt}
DAX Qualified Column - Primary Keys Drop down list Calendar[Date] {pqpk#:prompt}
DAX Qualified Column - Foreign Keys Drop down list Sales[Date] {pqfk#:prompt}
DAX Qualified Column - Non-Keys Drop down list Customer[Address] {pqnk#:prompt}
DAX Qualified Column - Calendar Only Drop down list Calendar[Month] {pqca#:prompt}
DAX Qualified Column - Calendar Primary Key Drop down list Calendar[Date] {pqck#:prompt}
DAX Qualified Column - Calendar Non-Keys Drop down list Calendar[Day] {pqcn#: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 * Full Row - Relative Pick row(s) from sheet 1:1 {pfrr#:prompt}
Formulas * Full Row - Absolute Pick row(s) from sheet $1:$1 {pfra#:prompt}
Formulas * Full Column - Relative Pick column(s) from sheet A:A {pfcr#:prompt}
Formulas * Full Column - Absolute Pick column(s) from sheet $A:$A {pfca#: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}
Formulas * Excel Table Name Drop down list Transactions {pxtn#:prompt}
Formulas * Excel Named Range or Array Drop down list rngTransactions {pxnr#:prompt}
Formulas (Python only) Query Drop down list Transactions {pqry#:prompt}

* Includes regular Formulas, LAMBDA and Python formulae

Manual Tagging

We definitely recommend using the menu system we provide to create your tags. Having said that, it doesn’t stop you from tagging the items manually if you want to, 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!

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:

  • <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.

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}“))

In addition to the ability to prompt for Text and Value prompts, Queries also support other relevant tags, which will prompt the user to choose from a list of those specified objects which exist in the workbook. They are defined in the following format:

  • Query: {pqry#:prompt}
  • Excel Table Name: {pxtn#:prompt}
  • Excel Named Range or Array: {pxnr#:prompt}

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:

  • 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}
)

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}
  • Full Row – Relative: {pfrr#:prompt}
  • Full Row – Absolute: {pfra#:prompt}
  • Full Column – Relative: {pfcr#:prompt}
  • Full Column – Absolute: {pfca#: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}
  • Excel Table Name: {pxtn#:prompt}
  • Excel Named Range or Array: {pxnr#: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:

  1. Right click the item -> rename
  2. Enter the new name for the item
  3. Click OK

Re-ordering Biblio Monkey Items

Manual re-ordering of specific items

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

Options to re-index the sort orders of the Biblio Monkey database

Re-ordering the entire database

While moving individual items one at a time works, there are times where you want to quickly re-order everything in the database into Alphabetical or Creation order. Should you need to do so:

  • Close the Biblio Monkey
  • Go to the Options Monkey -> Biblio Monkey
  • Click the appropriate Sort option (bottom)
  • Re-open the Biblio Monkey

These options work by re-indexing the sort order of the database as follows:

  1. Sort by ID: Re-sorts based on the order items were created
  2. Sort by Name: Re-sorts based on alphabetical order

And remember… you can always re-order individual items once you have applied this global sort.

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.
Moving a measure into a custom folder
Moving a measure into a custom folder
Our measure has now been moved into our custom folder
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.

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

Deleting Biblio Monkey Folders

To delete a Biblio Monkey folder:

  • Right click the folder and choose Delete

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:

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

Creating a New Database

The steps to create a new database are as follows:

  1. Choose “Create new database” from the Manage Database drop down
  2. Optional: Choose a new database name. (Note that this name must end with .accdb)
  3. Browse for the folder you’d like to store your database in.
  4. 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.

Choosing a Different Database

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

  1. Choose “Use different database” from the Manage Database drop down
  2. Click the Folder icon to browse for the database file you wish to use
  3. Optional: Configure the name/path manually
  4. 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.

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!

Share:

Facebook
Twitter
LinkedIn

Related Posts

Excel Feature Improvements

Article Overview Monkey Tools offers several Excel feature improvements to build a better user experience. While the vast majority of these features are custom-built to

Connection Monkey

Article Overview The Connection Monkey is your one stop shop in order to connect to the various Excel workbooks or Power BI models that Monkey

Global Options controlled by the Options Monkey include legacy PivotTable buttons, show the user interface in Free mode, Disable Enhanced Query Metadata Analysis and Error Logging

Options Monkey

Article Overview What is an application with Options? Monkey Tools is no difference, and we have a specific Options Monkey to help you control them

Export Model Data

Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time, we did feature this

Copyright Excelguru Consulting Inc.