Tag Archives: INDEX



Sum Last N Columns

If you have a data table that is updated frequently, for example, a new column is added each month, you may want to find the sum of the last three columns. But, you don’t want to rewrite your formula each time you add a new column. Fortunately, you can accomplish this task with two lookup functions, […]


Simulate Structured References in Named Ranges

The table feature introduced in Excel 2007 is amazing and has nearly eliminated the need to build dynamic named ranges since tables auto-expand. Beyond auto-expansion, tables offer numerous other benefits, including, structured references which allow us to refer to an area within the table, such as a specific column. In this post, we’ll examine a method […]


Date Data Validation Drop-Down

In this post, we’ll create two data validation drop-down cells that provide the ability to select From and To dates based on the transaction dates stored in the source data. This will ensure that the date selections fall within a valid range of dates, that is, those months with data in the table. This technique […]



Create Dependent Drop-down Lists with Conditional Data Validation

This post explores macro-free methods for using Excel’s data validation feature to create an in-cell drop-down that displays choices depending on the value selected in a previous in-cell drop-down. Overview As with just about anything in Excel, there are several ways to achieve the goal. This post explores three such solutions, and if you have a […]