Tag Archives: data validation

Creating Dependent Lists from a Column Lookup in an #Excel List by David Hager

When you have columns in a list that contain (for instance) state names and their corresponding cities, you might find it useful to create lists from the internal range of the subordinate column. For example, in the list shown here you might want to return the range from the City column corresponding to a specified state in the State column shown below.

 LookupFromTable1

The key to generating lists from the City column is the following formula.

TheCity=OFFSET(State,MATCH(TheStateName,State,0)-1,-1,MATCH(TheStateName,State,1)-MATCH(TheStateName,State,0)+1)

where State is the column in the list on the LookupSheet tab containing state names and

where TheStateName is the cell containing a data validation list of unique states (Control!C3).

where TheCityName cell (Control!D4) contains a data validation list of cities corresponding to the return value of TheCity.

Basically, what this formula does is return a range from the City column, given a specified state.

The following figure shows the cells with the data validation drop down lists.

 LookupFromTable2

The information from these cells can be used for further lookups, which is the subject of an upcoming post. You can download the example file here.

LookupFromTable

 

Advertisements

#Excel: A Model Using the CONVERT Function Containing Categories by David Hager

The CONVERT function in Excel allows the user to calculate from one type of unit to another. When you get to the 2nd argument in writing that formula, Excel provides an intellisense dropdown for the selection of units.

Convert1

Unfortunately, although the units are in category order, there is no way to see a category description.

The tables containing the units used by the CONVERT function can be found at the following link.

https://support.office.com/en-us/article/CONVERT-function-D785BEF1-808E-4AAC-BDCD-666C810F9AF2

That data was imported, messaged and cleansed to make the list found in the Info worksheet, adding a category for each record in the list. I then used the Advanced Filter to make a list of unique items from column A. That is the primary list used in the calculation model (on the Convert worksheet).

Type=Convert!$C$2

And is used as a data validation dropdown in

Category=Convert!$C$2

The workbook also provides a good example of creating dependent dropdowns from a list. A great formula used to do this is shown below:

CategoryDep=INDIRECT(“Info!C”&MATCH(Category,Info!$A$1:$A$93,0)&”:C”&MAX(IF(Category=Info!$A$2:$A$93,ROW(Info!$A$2:$A$93))))

This data validation list formula is used in the dropdowns in E2 and F2.

From =Convert!$E$2

To =Convert!$F$2

In the example workbook, the cells that are formatted as light blue have Data Validation dropdowns that allow categories to be used with the unit selections for the CONVERT function. So, the dropdown in C2 is used to select the category, and the dropdowns in E2 and F2 are used as the 2nd and 3rd arguments in the CONVERT function in cell A2.

Note that this technique does not include the Prefix and Binary Prefix categories.

It is also important to note that since the unit information used here is in a worksheet list, we are not bound by the examples provided by Microsoft. However, a different calculation mode would have to be used, since the custom unit symbols would not be recognized by the CONVERT function. I plan to look at using a formula like =PRODUCT(number,multiplier) for the custom categories. Feel free to use this idea to build your own custom examples. I will be working on it, too 😊.

You can download the file here.

convert2