I just found out you can split the VBA code window. See images below. Use the small icon above the right side scroll bar. This can be useful if you have a long block of code and need to look at separate parts together. Thanks to Bob Umlas the Excel MVP for sharing. You can double click the split bar to remove it.
Excel’s EOMONTH function is great. Here is a tweak using a custom function to calculate the number of days in a month.
(more…)
If you have the subscription version of Excel then you have access to the Navigation Pane. This allows you to navigate between sheets and see the structure of the sheets in your file.
(more…)Here's a technique I use a lot to speed up report development. Sheet names have to be unique, so they can't be duplicated. This makes them great for department names or states. This short video combines a few techniques to extract from a data set based on the sheet name. All in less than a minute. https://vimeo.com/780315328
Excel has three functions that can rank values. Unfortunately none of them provide a way to create a sequential list of ranking numbers when there are duplicates. Luckily we can adapt the RANK function to handle duplicates and create a sequential list of ranking numbers.
(more…)
In Excel you can use the Go To Special dialog to find constants. These are cells that won’t change. Constants are things like labels, entered text, numbers, or dates. But there are cells that won’t change that Go To Special won’t identify as a constant.
(more…)