Advanced

Check the Analysis ToolPak Add-In in Excel 2003 [VBA Code]

February 28, 2012 Advanced

I recently began using the NETWORKDAYS function in Excel 2007, yet my file was going to be widely used by people with Excel 2003 (both are Windows versions). The NETWORKDAYS function is a standard function in Excel 2007, but with 2003 you have to install the Analysis ToolPak, otherwise you get an error. The only [...]

Get the full story …

Select a Column of Non-Sequential Data

December 19, 2011 Advanced

In one of my posts on using VBA to select a column of data, I received a comment asking if the macro could be modified to select data like the screen shot you see to the left. This data is non-sequential and consequently the CurrentRegion property won’t work. The way around this is to select [...]

Get the full story …

Microsoft Query Incompatible with Mac OS X Lion [Updated]

July 12, 2011 Advanced

The other day I read an article on how to prepare my MacBook Pro for Lion, the new Mac OS X coming out this month, and discovered that any application that requires PowerPC is incompatible with Lion and will not work. See update at the end of this post. So I followed the instructions to [...]

Get the full story …

International Short Date Formatting for the TEXT Function

July 7, 2011 Advanced

I have an Excel workbook that is used in two different regions where the date format is entirely different, the US and the UK. At the top of a report worksheet I use a TEXT function to inform the user of the date range. Here’s what a US user sees: From: 6/6/2011 to 6/10/2011 Here [...]

Get the full story …

Select One Column of Data with VBA

May 4, 2011 Advanced

Let’s assume you aren’t using an Excel Table, List Object, or List for your data table and you want to select the data in only one column, which means the Header row is excluded. This can be simple if there’s data in every row of the column, but what if that’s not the case? And [...]

Get the full story …

The Table Address and Structured Data in Excel

April 19, 2011 Advanced

The data in an Excel Table is nicely addressed. Meaning when you ask Excel for the address of the Table you get the data only and not the Header row or, if toggled, the Total Row. The Table above starts in row 4 of the worksheet with a Header and ends in row 43 because [...]

Get the full story …

Speeding up VBA with the PrintCommunication Property in Excel 2010

March 23, 2011 Advanced

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" [...]

Get the full story …

Automatically Expand a Named Range in Excel

December 22, 2010 Advanced

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 [...]

Get the full story …

Calculate the Xth Weekday of Any Month in Excel

December 8, 2010 Advanced

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 [...]

Get the full story …

Single-Cell Array Formula in Excel

November 13, 2010 Advanced
Thumbnail image for Single-Cell Array Formula in Excel

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. [...]

Related Posts Plugin for WordPress, Blogger...
Get the full story …

← Previous Entries