Horizontal Dynamic Dependent Drop Down List Example

A Dynamic Dependent Drop Down List with a Horizontal Table Reference

I received a comment asking if a dynamic dependent drop-down list in Excel could have a list where the “table headers were actually rows and not columns?” Since I’ve already detailed how this is done in the article mentioned above, I’ll keep this short. The screen shot below is what I’ll be referencing. At the …

Read more

How to Update a List or Range without OFFSET

I avoid the use of Volatile Functions, especially OFFSET, which is commonly used to update a list or range. They can slow down the operation of your workbook. For very large workbooks with lots of data, it can be significant and irksome. Worksheet cells that use Data Validation for a drop-down list can simplify the …

Read more

Select Excel’s Used Range on a Mac

I recently read a good blog post over at Contextures about selecting the actual used range on an Excel sheet, both manually and with VBA. However, using Excel on a Mac makes you keenly aware that there’s no Home button. The used range on a worksheet starts with cell A1 and ends with the last …

Read more

Pay Periods and Funky PivotTable Controls

I like to use a PivotTable to figure out simple problems in Excel. So for this post I’m going to use Excel 2011 (Mac), where PivotTable controls look funky when compared to their Windows counterpart. Since I get paid every two weeks, certain months in a year will contain three pay periods. Planning future vacations …

Read more

Fill Down a Formula with VBA

I commented on a post that brought to light, the fact that, using the cell fill-handle to “shoot” a formula down a column doesn’t always work when the adjacent column(s) have blank cells. So I decided to share some Excel VBA code that’s used to copy a formula down to the bottom of a column …

Read more

A Dynamic Dependent Drop Down List

A Dynamic Dependent Drop Down List in Excel

The other day I was reading a post over at the Contextures blog about Dynamic Dependent Excel Drop Downs and realized that using an Excel Table would provide an alternative method that is both simple and flexible. Tables are available in Excel versions 2007, 2010, and 2011. In this post I’ll create a Table to …

Read more

Dynamic Table Reference with INDEX

Tables have structured data so you can easily use column references in formulas. But with the Table name AND column name, the reference can be rather lengthy and hard to understand at first glance. As an alternative, you can simply create a dynamic column reference in a Table by using the INDEX function in a …

Read more

Total Row Formulas in Excel Tables

Excel Tables have a Total Row that can be toggled on and off from the Ribbon. Several functions are available when the Total Row is turned on. In Excel 2010 and 2007 (Windows) select a cell inside the Table, then choose Table Tools and click the check box for Total Row. In Excel 2011 (Mac) …

Read more

Formulas and Structured Data in Excel Tables

I created a data table on a worksheet, then converted it to an Excel Table. Before the conversion, I had entered a formula for Gross Product in the last column. This formula looks like most formulas do in Excel, with cell references, as pictured below. The Gross Product formula in cell L5 is =K5+J5+I5. The …

Read more