Advanced level information
I have an Excel file that creates different reports from a data table using a macro, and before Excel 2010 was released the “time-hog” was running VBA code for the print settings. Tell me you’ve added something like the following code and had your execution time slow exponentially. With ActiveSheet.PageSetup .CenterHeader = “&””Verdana,Bold””&12&A” .CenterFooter = …
I usually put a name to each data table created for referencing information in in Excel, as in a Named Range. If you add data to the bottom of the table, the Named Range isn’t modified and any reference to it will fail to include the new information. Here’s a table of data I stuck …
I’ve a simple formula for calculating the Xth Weekday of ANY month. It takes four inputs: Year, Month, Week, and Day. And requires a couple of lookup tables for data validation, one with special formatting. The History It all started when my wife mentioned there were a couple of meetings she had to schedule at …
A Single-cell Array formula is powerful, yet can be hard to understand. The Evaluate Formula dialog box helps by revealing array values Excel holds in internal memory. In the spreadsheet you see pictured, a single-cell array formula for the Cumulative Plan is located in cell B2. It summarizes the historical and current day’s Plan data. …
If you’ve discovered how useful Names can be in a spreadsheet, you may also know they can get-in-the-way-if-you-have-too-many. Just check out the Name Box in the picture to the right. Each Name has a Visible property that’s set to TRUE by default. You can hide a Name by changing the Visible property to FALSE, but …