User Defined Functions (UDFs) are functions that can be inserted directly into formulas within cells just like SUM, AVG, VLOOKUP and the other built in functions. The difference is that the functions are custom made to your unique calculations.
In this project, we developed a set of User Defined Functions (UDFs) for a client who was constantly using the same formulas over and over again. They used these functions to help convert data from an old legacy system to a different format that matched their new sales system. This allowed them easily merge reports from both systems without having to rewrite the same functions over and over. In the image, you can see the old legacy part numbers in column A and the legacy city in column B. The UDFs we designed converted the format of the part number to look like column C and actually did a lookup of the city to determine the region in column D. If you look closely at rows 5 -7, you can actually see the UDF functions being used in the formulas in column C and D.
See a video demonstration of this project here.

Dashboards and Report Cards can be great tools to help manage a business. A great dashboard is one that shows key summary information in an easy to ready graphical manner. The data for dashboards generally comes from one or more internal systems such as accounting, production or sales systems and can be quickly and easily updated.
In this example, a dashboard was created for a large company. The dashboard shows goals and actual numbers for key finance and production metrics. The goals are set on various worksheets within the system. The actual numbers are imported from monthly accounting reports. The user simply presses a button on the spreadsheet and then tells the system which file to import. The file details are then imported into the system. The system then cross references the data and pivots it to get the summary details needed and matches them with the goals. This data is then added to the graphical summaries so that managers can see at a glance how projects and departments are performing. The system was designed to aggregate financial details over many years, with the data being updated monthly, proving a real time, long term view of performance.

Excel is great for creating financial models and calculators. These are some of our most frequently requested items. In this project, we had a client who wanted help creating loan repayment schedules and calculating effective interest rates for loans with one borrower and many different lenders. Because it is a blended loan, regular online calculators simply wouldn’t work. But we were able to put together a solution that helped them figure out many details including how to handle rounding, carryover of partial pennies and other issues specific to their project.

Here is an example of integrating Excel with an external database and custom forms. Using a database has many advantages. Databases are great for handling large and interconnected sets of data. They also allow multiple people to use and update the data at the same time. In these kinds of projects, Excel basically becomes the front end software that interfaces with the database and allows for interaction – viewing and changing the data.
In this example, the system tracks over 20,000 parts for an eRetailer. They often have 4 or 5 people connected to the system at the same time. To get the latest data, they simply have to Refresh their sheets and continue working. This system was originally built without a database. As the company grew, it became clear that a more robust solution was needed and the database was added.
We also added custom user forms (windows) that open up to display specific data and to allow for easy data entry. The forms are also used to validate data entry, making sure that the correct data is captured and stored in the system.
This system also manages over 100,000 part photos. As photos are added to the system, the photos are automatically renamed and moved to an organized location. When a part is ready to be listed, the photos are automatically uploaded to servers so that the photos can be shown in eCommerce listings.
See a video demonstration of loading data from a database here.

Here is an example of tool we did for a client to import and manipulate data from another system.
A client was manually processing data files from a piece of lab equipment. This included adding various baseline adjusting calculations, adding second derivative calculations and creating multiple charts and graphs. In all, the client was spending 20 minutes or more on each data file and often repeated the process 3 to 5 times a day.
We developed a custom Excel add-in that was added to every computer in their facility. The add-in had all of the code necessary to automate the entire process and cut down their 20 minutes of work to just seconds. All they had to do was select their custom macro from the tool bar and pick the file they wanted to process. The custom VBA macro did all the rest of the work. At last count, they have been using the system for over 5 years, saving thousands of hours of inefficient work.
See a video demonstration of this project here.

One of our clients needed a custom scheduling application to help the plan their projects. Their company already had information in spreadsheets and just needed our help to streamline part of the process and to provide a nice graphical representation of their schedule in a Gantt Chart.

We developed this workbook for a company that needed a cost effective way to keep track of and calculate workers time sheets. Their business is heavily regulated and has some interesting rules which required a custom solution instead of an off-the-shelf system.

This workbook helps a client manage their entire Ebay store. The system tracks thousands of parts as they move through the companies processes. The workbook interfaces with a database on a server where all the information is actually stored, allowing multiple users to access the same information simultaneously. Tools have been developed that allow users to manage information and automate tasks such as photo management, pricing and listing.

We put this spreadsheet together to provide an interface for communication with a device. The spreadsheet communicates serially with a device and can issue commands manually or automatically. It then logs all the responses from the device. This spreadsheet provided a quick and flexible way to integrate with lab equipment under development.