Excel

Before progressing on to the other puzzles in the HR Compliance reporting that I started in the last post, I wanted to provide another example of using arrays as a searching tool when VLOOKUPs or INDEX/MATCH do not work. The more times you use this kind of array formulas, the easier it is to understand….

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…

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…

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…

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…

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…