Home / Archive by category "Excel"


Listening to My Customers

Listen and Collaborate
We're on our third year with our automated operational reporting, which has been rolled out to both sides of the house. It kind of gives me a secret thrill to think that once a week, everyone comes in to work in the morning, opens up the file and lets the program do its magic. And if they have done what they needed to do during the past week, the report is done in about five minutes. Now...
Read More

New Idea: A self-correcting Excel file

Exploring a New Idea
I did something I have never seen done before: I created a self-correcting spreadsheet. In the last few weeks I've been working on generating a solution to pull together and print out state government regulations, covering certain topics such as minimum wage, meal and rest breaks, overtime, etc. My company probably pays to receive this data from another company and the data comes in down...
Read More

Automatic Printing to PDF: Challenges with Repeating Headers and Page Breaks

Special Printing from Excel
So far, on this latest project, I have figured out how to handle varying spelling of state names when a normal VLOOKUP or pivot table won't do. And I got through the issue of varying row heights in the downloads. The posts can be found below and each contains a copy of the Excel file so I won't rehash those:A Solution for Looking Up Things When a Simple VLOOKUP Won't DoPart 2 of Using Ar...
Read More

Workaround for Autofitting Rows and Merged Cells

Excel: Row Heights and Merged Cells
Have you ever tried doing an autofit of a row by placing your mouse to the far left of the file, to the bottom of the row, and right clicking your mouse and nothing happens? It might be because you have merged cells in one of the columns.But sometimes you need those merged cells. For example, I'm building an "automated" report that pulls in the requisite information for various sections ...
Read More

VLOOKUPS and Line Breaks

Solving Row Heights 1
Have you ever done a VLOOKUP only to find that the words run into each other, but when you look at the source, the words are beautifully spaced? You end up with something like: "Priced by volume:$100 buy in volume of 200$150 buy in volume of 300$200 buy in volume of 500"? If you look at the source origin, you will find that line breaks were made within the cell to make the layout fine fo...
Read More

Part 2 of Using Array Formulas

Solving Puzzles 2
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. Arrays are not something that are commonly used, at least in places where I've worked...
Read More