Home / Archive by category "Excel"


A Solution for Looking Up Things When a Simple VLOOKUP Won’t Do

Some Solutions for Reporting
This post will go over some of the solutions I've thought of to enable automating the HR/Legal Report. I haven't finished yet, namely because there are a bunch more downloads that I'm supposed to receive, and because there are a few issues I still need to work out, but I thought I would provide a work-in -progress look. These downloads are any downloads that any company would get so they...
Read More

Interesting Challenge: Automatically Pulling in Legal Information into a Readable Document

New Challenge: HR Report
This week I got a new interesting challenge. My HR VP needs to have an easier way of updating HR legal information that comes in about two times a year. She either receives or downloads the information, of which there are maybe about 12 separate pieced of information.  I received 4 as examples. Since the company has grown and now operates in multiple states, there is a greater need of fi...
Read More

Testing My Macros and Failing

Testing My Macros
There were five solutions to add to my boss' machine and the first set of macros for the first solution (automation of filtering proper GL accounts when a site is chosen) did not work. Oops.These macros weren't just any ol' macros - they were macros to go into the PERSONAL.xlsb file and they were event handler type of macros (one was a class macro). I was trying to do the "easy" way for ...
Read More

Breakthrough on Testing Excel Files

Breakthrough in Automating - Partially - Testing
In my last post I mentioned that I was testing a revamped spreadsheet and was having a difficult time of it because the testing required detailed testing of each cell. The revamp was extensive and the testing is massive (large number of cells and probably at least 15 tabs to go through). I'm not really good at repetitive stuff which is why I try to automate such activities - either by fo...
Read More

Testing a Revamped Budget File

Testing Software
The past week I've been reviewing a revamped budget file. The budget file is totally revamped which means extensive testing is required. I was able to design an automated checking of whether cells are appropriately locked (or unlocked) using the CELL("protect", ref) function. I also have a user defined function CellFormula (a macro) that shows the cell formula as text but I haven't yet s...
Read More

Using Excel’s Userforms

Excel Userforms
I keep learning new things. Lately, my boss wanted an easy way of "printing" her budget file as an Excel file but without formulas to send to our customers. Previously, she sent the budget file as a PDF but you know you can't use the information in Excel unless you have the professional version of Acrobat to convert the file from PDF or you key in the data into your own file. She needed ...
Read More