Excel Video 90 takes a quick break from OFFSET to show you how to create a drop-down box like the one in Excel Video 89. Hopefully you saw the drop-down box I created as part of Excel Video 89 to show the components of the OFFSET function for Year, SanDiego, and LosAngeles. There are three tricks I used in creating the drop-down box.
First, select the cell you want the drop-down box in, then go to the Data tab and choose Data Validation. In the drop down box labeled Allow, choose List. Then tell Excel where the list of acceptable values for the cell is. In our example, it’s the cells with Year, SanDiego, and LosAngeles.
Once you have the drop-down box ready, the next trick is to use HLOOKUP since the data I want to look up is stored in rows. The structure of HLOOKUP is very similar to VLOOKUP that we reviewed several videos ago. I told HLOOKUP I wanted to find the cell we validated in the previous step, so we know the cell will be either Year, SanDiego, or LosAngeles. The next step in HLOOKUP is to give the range where the data is stored.
The final trick is to use the ROW() function. ROW simply returns which row the cell is on. By figuring out what row we’re on and then subtracting 23 from that row, we get the correct row in the table for HLOOKUP to return. For example, for the formula on row 25, we want the Reference, which is stored in row 2 of our data range. Subtracting 23 from 25 not only gives us row 2 that we need, it’s also a formula we can copy down so that when we’re on row 26, subtracting 23 will now give us the third row in the data table for HLOOKUP. By structuring the HLOOKUP with ROW, we can create one formula in cell B25 and copy the rest of the formulas down.
We’ll get back to OFFSET next time with a clever way to show the last x data points in a chart. Once that works, we’ll be able to change x to increase or decrease the range of the chart in several cool ways. Stay tuned.
