Excel University Blog

Read on for in-depth articles, tutorials, and videos. Search or browse for specific topics. Be sure to subscribe if you'd like to be notified when we write something new.

INDIRECT

List of Worksheets in a Drop Down

By Jeff Lenning | May 27, 2020 |

In this post, I’ll answer a question I received from Sara about how to create a drop down with each worksheet name and reference it in a formula to retrieve values from the selected sheet. As this is Excel, there are a variety of alternatives to accomplish this. In this post, I’ll demonstrate one such…

Read More

VLOOKUP Hack #5: Different Tables

By Jeff Lenning | November 15, 2017 |

We are right in the middle of a blog series called VLOOKUP Hacks. We started by exploring the 4th VLOOKUP argument. Then we hacked the 3rd argument. Now, as you may have imagined, it is time to hack the 2rd argument. In this post, we’ll allow the user to retrieve values from different lookup tables.…

Read More

Income Tax Formula

By Jeff Lenning | December 31, 2014 |

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…

Read More

Indirectly Refer to Table Columns

By Jeff Lenning | November 20, 2014 |

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…

Read More

Referring to Tables Indirectly

By Jeff Lenning | November 13, 2014 |

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…

Read More

Create Dependent Drop-down Lists with Conditional Data Validation

By Jeff Lenning | February 7, 2014 |

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…

Read More