In this post, we’ll create a drop-down that contains a unique list of choices derived from a column that contains duplicate values. This may sound familiar as we previously accomplished this with a PivotTable. However, the Power Query feature that’s built-in to Excel 2016 makes this process easier. Objective We have a data table that contains RepID, […]
In this post, we’ll perform a two-dimensional lookup with Excel’s VLOOKUP function. Objective Let’s begin by clarifying our objective and what is meant by the term two-dimensional lookup. We have stored our price list in a table, and the price for each item varies based on the region. This is illustrated in the screenshot below. To […]
Beginning with Excel 2013 for Windows, we can use Slicers to filter table data. In this post, we’ll explore how to use Slicers as a relatively easy alternative to conditional drop-downs. Objective When we want to allow a user to select a choice from a list of items, we often consider using the Data Validation feature to […]
In this post, we’ll examine a couple of ideas for computing income tax in Excel using tax tables. Specifically, we’ll use VLOOKUP with a helper column, we’ll remove the helper column with SUMPRODUCT, and then we’ll use data validation and the INDIRECT function to make it easy to pick the desired tax table, such as single or married […]
Previously, we explored using the INDIRECT function to refer to various tables in a workbook. In this follow-up post, we’ll expand the discussion and refer to individual table columns. Objective Let’s start with our objective. We have several tables in a workbook. They have the same structure and store department data. For example, here is […]
In this post, we’ll allow the user to select a table name from a data validation drop-down and our Excel formulas will operate on the values from the selected table. Thanks to Neelima for asking about this technique! Objective Before we get too far, let’s be clear about our objective. We have several data tables in […]
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 […]
In this post, we’ll explore a method to allow a user to select an item from a drop-down list and then use formulas with the VLOOKUP function to retrieve multiple values from the related item table. Objective Before digging into the mechanics, let’s review our objective with an example. We would like to allow our […]
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 […]
In this post, we’ll explore a method for generating a drop-down that contains a unique list of choices derived from a table column with duplicate values. Objective Before we get started on the mechanics, let’s take a look at what we are trying to achieve. We have a data table that is exported from our […]