InLook Add-in
Introduction
AutoByd InLook is a powerful Excel Add-in that simplifies tracing of precedent/dependent cells and helps in saving time exponentially.
[Continue scrolling down for a detailed walkthrough]
Ribbon tab
InLook's ribbon has four main buttons:
Show Precedents: Shows a list of particulars, values and addresses of all precedent cells of active cell. You can also access this by using the shortcuts Ctrl+Shift+[ or Alt S S P
Color Precedents: Colors all precedents of all selected cells with chosen color. You can also access this by using the shortcut Alt S C P
Show Dependents: Shows a list of particulars, values and addresses of all dependent cells of active cell. You can also access this by using the shortcuts Ctrl+Shift+] or Alt S S D
Color Dependents: Colors all dependents of all selected cells with chosen color. You can also access this by using the shortcut Alt S C D
Form shortcuts
Clicking on "Show Precedents" or "Show Dependents" button will display a form as shown in image above. While the form is showing up, the following shortcuts may be used:
Enter - Minimizes the form and navigates to the selected cell link in the list.
Mouse double click – (same as Enter)
Space – Minimize/Maximize the form
O – Displays a message with full address and formula contained in the original cell.
A – Displays a message with full address of the selected cell link.
F – Displays a message with formula contained in the selected cell link.
C – It colors the selected cell link with default color as chosen in Add-in settings.
X - It removes color from the selected cell link.
Escape – It closes the form and navigates to the original cell where macro was first run.
Add-in settings
Modify the add-in settings by clicking on "Settings" button on the ribbon. Each setting is discussed below separately.
Default color - Sets the color which has to be used when shortcut key ‘C’ is pressed while precedents/dependents form is showing up.
Lookup method - This determines what text to display under “Particulars” column of precedents/dependents form. The three available methods are:
Automatic - It uses pre-determined set of rules to fetch the “Particulars” value of corresponding precedent/dependent cell.
Horizontal - This method ignores all column headers and displays only row headers. As a result, it will fetch “Particulars” value that exists to the left of respective precedent/dependent cell.
Manual - By choosing this method, you can set custom row and column offset values to determine which “Particulars” value to fetch.
Keeping in view of a huge variety of data structures, the Add-in has also been equipped with "Custom IPF" functionality - discussed under Developer's reference section.
Amount formatting - It formats the amounts to be shown under “Value” column of precedents/dependents form. The four available options are:
Indian Rupees - It formats the amounts as per Indian comma format.
[For example: 48,52,65,000]
Millions - It formats the amounts as per Million comma format.
[For example: 485,265,000]
No Format - It removes all number formatting (including precentage % formatting) and displays plain numbers.
[For example: 485265000]
Hybrid Format - It separates the crore’s part of amounts with “CR” & formats the remaining parts of amounts as per Indian comma format. This enhances the readability for the users and especially helpful when huge numbers are present in the reports.
[For example: 48 CR 52,65,000]
Rounding off - If this option is checked, amounts under “Value” column of precedents/dependents form will be rounded off to specified number of digits. Round off can be made upto 4 digits.
Precedent levels - This determines how the precedent cells are to be displayed on precedents/dependents form. The two available modes are:
As Is mode - It displays all precedent cell addresses as they are showing up in formula bar. All direct precedents will be listed first. All other precedents will be listed next in their respective order.
[For example: B5, C6:C8, D5, G1:G3, H5]
Expanded mode – If this mode is opted, precedent cell addresses will be shown in same manner as discussed under “As Is” mode. However, in addition, this mode further breaks down all continuous ranges into single cells.
[For example: B5, C6, C7, C8, D5, G1, G2, G3, H5]
Cell coloring method - If “Interior” is opted, then cell interiors will be colored. If “Font” is opted, then cell fonts will be colored.
Developer's reference
Key to using the "Custom IPF" functionality: (for Advanced users)
Displaying the "Particulars" of a corresponding precedent/dependent cell is what makes the add-in very unique and powerful. Even though the add-in offers three types of Lookup methods (viz. Automatic, Horizontal, and Manual) still in case of complex report structures, the user might be in need of a custom function to return the "Particulars" value.
The good news :) is add-in has a provision for writing custom functions in Visual Basic (VBA) language to fetch "Particulars" value as per user's requirements.
Here's how to do it:
Create a public function with name IgnoreInlook
This decides when to ignore Inlook add-in's algorithm and use custom user-defined function in VBA to fetch "Particulars" value.
Declare the function as "Boolean". The function has one argument to be declared as "Range".
[For example: If the user has a complex report only on one specific sheet then he may opt to ignore inlook only on that particular sheet.]
Create a public function with name InlookParticularsFetcher
This determines what text to display under “Particulars” column of precedents/dependents form.
Declare the function as "String". The function has one argument to be declared as "Range".
[For example: User can use the full functionalities and capabilities of VBA to fetch "Particulars" value by using offsets, cell characteristics, native lookup functions, concatenating multiple cell values, etc.]
For more practical guide on using the "Custom IPF" functionality, you may download the sample files by clicking here.