Tag Archives: Names

Horizontal Dynamic Dependent Drop Down List Example

A Dynamic Dependent Drop Down List with a Horizontal Table Reference

I received a comment asking if a dynamic dependent drop-down list in Excel could have a list where the “table headers were actually rows and not columns?” Since I’ve already detailed how this is done in the article mentioned above, I’ll keep this short. The screen shot below is what I’ll be referencing. At the end of the post I’ll give a link to the file I used.

Conditional Drop Down List (Excel)

Horizontal Dynamic Dependent Drop Down List Example

There are two named ranges,

    1. 1)

myCategoryH

    1. that refers to the range E1:E3 and

 

    1. 2)

myTableH

    that refers to range F1:G3.

A defined name, myItemListH, is created with the following formula =INDEX(myTableH,MATCH(Sheet1!A2,myCategoryH,0),0) that will return a row that is matched by the contents of cell A2.

In cell A2, add a Data Validation list with the source being =myCategoryH. In cell B2, add a Data Validation list with the source being =myItemListH in the conditional drop down list from Excel.

Now you’re done.

Cell A2 will give you a drop-down list of Fruit, Vegetables, or Other Stuff. And cell B2 will read the value in cell A2, match and return the proper row number, and return an array of values for that row.

Two Ways to Use the INDEX Function to Return an Array

This is a simple case of using the INDEX function in a slightly different way. Normally, to return column 2 of a named range, you would use the following formula =INDEX(myTable,0,2). The zero means: return all rows (of column 2).

If you want to return row 2 of the named range you would use =INDEX(myTable,2,0). Here the zero means: return all columns (of row 2).

This comes from the Help section of the INDEX function where in Excel 2011 it reads:

If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula in a horizontal range of cells for a row, and in a vertical range of cells for a column. To enter an array formula, press ⌘+RETURN.

Practical Considerations

Adding to each category list across columns is problematic. For one thing adding more data to the table will require inserting a column within the table range to avoid renaming the range. Obviously a standard Excel “Table” won’t work with this type of list. And if you have a very large spreadsheet the number of columns will become limiting long before the number of rows would.

Download the File

Horizontal-Dynamic-Dependent-Drop-Down-List.xlsx

A Dynamic Dependent Drop Down List

A Dynamic Dependent Drop Down List in Excel

A Dynamic Dependent Drop Down ListThe other day I was reading a post over at the Contextures blog about Dynamic Dependent Excel Drop Downs and realized that using an Excel Table would provide an alternative method that is both simple and flexible. Tables are available in Excel versions 2007, 2010, and 2011.

In this post I’ll create a Table to hold the Category’s and Items, create three defined names using dynamic formulas, then use Data Validation to create two drop-down lists, the second being dependent upon the first.

Create at Reference Table

Here’s a Table with Category names in the header row and Items in the columns. I just typed in the information then converted to a Table.

Category and Item Table

Create a Dynamic Defined Name for Category List

Create a defined name for the Table1 Header row range by using the formula =Table1[#Headers]. Please note that Table1 is the name of the Table created in the step above.

This defined name is dynamic, meaning it will expand when more columns are added and shrink if any columns are deleted. It will return the header row of the Table, which we’ll use in the next step. I used myCategory for this defined name.

Create Category Defined Name

To create a defined name in Windows choose Formulas > Name Manager then click New…. On a Mac choose Insert > Name > Define…. This will bring up the New Name dialog box that looks like the Edit Name screen-shot shown above.

Create a Category Drop Down List with Data Validation

Type Category in cell A1 for the column heading. Next select cell A2, then choose Data > Data Validation > Data Validation… and in the Data Validation dialog box (shown below) select List from the Allow box, then type in =MyCategory in the Source box, and click OK.

My Category Data Validation

Now cell A2 has a drop down button that shows the Header row of the Table. (Don’t worry about extending this Data Validation down to more rows, we’ll take care of that later.)

Category Drop Down List

Column B will hold a drop-down list for the Item, which is dependent upon the Category. This takes two defined names to work properly.

Create the First Defined Name for Items

[Update: Select cell B2 before you follow this next step.]
Create a defined name with the following formula:

=INDEX(Table1,0,MATCH(Sheet1!A2,Table1[#Headers],0))

that I’ll name myItemList.

My Item List Defined Name

This formula will return a reference to the Table column that matches the Category selection in cell A2 on Sheet1.

If I use Data Validation to create a drop-down list with the myItemList defined name I’ll get eight items returned because Table1 has eight data rows. As you can see in the picture below, the Vegetables item list has two blank lines, and the Other Stuff item list has one blank line.  Not an elegant solution.

Table Column Full List

However, if you have a table that always has equal items (rows) in each column then this defined name formula will will work well for a Data Validation list.

Create a Second Defined Name for Items

We can create a second defined name that will give us a dynamic list with the exact count. The following OFFSET formula will do the trick.

=OFFSET(myItemList,0,0,COUNTA(myItemList),1)

This formula uses the first defined name (myItemList) but alters the height by counting the items. I named this defined name myItem, as you can see below in the Name Manager screen shot.

Name Manager for myList

Create an Item Drop Down List with Data Validation

Type Item in cell B1. Select cell B2 and open the Data Validation dialog box. Choose List and enter the following formula =myItem, then click OK.

Data Validation Item List

The result is a dynamic drop-down list in the Item column that’s dependent on the Category selection in column A, and returns the exact list.

Dynamic Dependent Drop Down List

Convert to a Table

Now it’s time to covert this to a Table, and by doing so the Data Validation will be preserved and automatically expand with the addition of more rows.

Select cell A2, then in Windows (Excel 2007, 2010) choose Insert > Table, verify the information in the Create Table dialog box, and click OK.

Create Table in Windows

On a Mac (Excel 2011), select cell A2 then choose Tables > New > Insert Table with Headers.

At this point I normally turn off the Data Filter because it’s rather annoying.

As your Table expands, with more rows or columns, this dynamic drop-down list will work just fine. To create a new row in the Table with Data Validation press the Tab key while the active cell is the last column of the last row.

Potential Problem with OFFSET

As you might have guessed the OFFSET formula depends on having items at the top of the list. Should your data have blank rows in the middle of the column, the drop-down won’t have all the items listed yet show blank cells in the list.

Blank Cells and Missing Data

In this case it’s best to use the first defined name (myItemList) in creating a dependent, drop-down list for Item. At least you’ll get all the data, even if it does have some blanks in the list.

Blank Cells and No Missing Data

Reference Table Location

For illustration purposes I’ve shown the reference Table on the same worksheet as the Category and Item Table. Normally I would place the reference Table on a different worksheet. In this instance none of the formulas would change.

[UPDATE]

I’ve had numerous questions about the details of this post so I’m putting a link here to download the file.

Hide or Show Names in Excel with VBA

If you’ve discovered how useful Names can be in a spreadsheet, you may also know they can get-in-the-way-if-you-have-too-many. Just check out the Name Box in the picture to the right.

Each Name has a Visible property that’s set to TRUE by default. You can hide a Name by changing the Visible property to FALSE, but only in the VBA Editor.

Hide Names in Excel

If you have a great many names to hide, it’s best to loop through the Names collection and change the Visible property value to TRUE for all Names.

Sub HideAllNames()
Dim n As Name
For Each n In ThisWorkbook.Names
If n.Visible Then n.Visible = False
Next n
End Sub

After running this code, you now have a blank Name Box.
Excel Name List Blank

If you just want to hide a few Names, the code below, executed in the Immediate Window of the VBA Editor, will do the trick. Just change out “MachName” for each Name you want to hide.

Hide Name with VBA Code in Immediate Window

Go To Hidden Names

You can still Go To a Name that is hidden, just type a Name in the Name Box and hit enter.

Show Hidden Name with Name Box

Or use the keyboard shortcut Ctrl+G or F5 to bring up the Go To dialog box, type a Name in the Reference text box, and click OK.

Show Hidden Name with GoTo Dialog Box

If the Name’s corresponding range address is on a hidden worksheet, this won’t work.

Show Names in Excel

While working on a file, it can sometimes be advantageous to actually SEE ALL the Names. The following VBA code will do just that.

Sub ShowAllNames()
Dim n As Name
For Each n In ThisWorkbook.Names
If n.Visible = False Then n.Visible = True
Next n
End Sub

Keep Some Names Visible with Code

Sometimes you may want to leave a few of the Names visible for users. In the code below I’m looping through the Names collection, setting the Visible property to False, then checking to see if any have “Lookup” in the Name property string and making those Visible.

Sub ShowSomeNames()
Dim n As Name
For Each n In ThisWorkbook.Names
n.Visible = False
If InStr(1, n.Name, "Lookup", vbTextCompare) > 0 Then
n.Visible = True
End If
Next n
End Sub

I use the VBA Function InStr, which stands for In String, to check and see if the Name Property of each Name has “Lookup” contained in it, by starting at character 1. If the InStr Function finds “Lookup” with in the text, it returns a numerical value, which represents the character position of where “Lookup” starts within the text string. If the InStr function doesn’t find “Lookup” within the Name Property, it returns a zero (0).

In the code above, if the InStr function finds “Lookup” within the Name Property string, the code sets the Visible property to TRUE. Here’s what shows in the Name Box after running this code.

Excel Name List INSTR Lookup

You can open up the Excel VBA Editor, create a new module, copy any of the first two code snippets, paste into the module, and it should run just fine. The third snippet is more specialized and would need modification of the “Lookup” attribute for it to work.

My apologies for not indenting the code above, but I’m still learning HTML and CSS.

Enter a Constant Value in a Name

Once upon a time I was working with an unfamiliar Excel spreadsheet and came across a formula that used a Name, which I figured for a constant value and thought it hidden somewhere in the worksheet, yet couldn’t find it anywhere.

It never dawned on me that a value doesn’t have to reside in a worksheet cell.

Enter a Constant Value in a Name — Excel 2007, 2010

You can put a constant value into a Name, which Excel will hold internally.

Go to Formulas, and select Define Name (Excel 2010, 2007)

Named Constant Menu Selection

In the New Name dialog box:

  1. Enter a Name into the Name field (no spaces, please)
  2. Select a scope: Workbook or specific worksheet
  3. Click inside the Refers to text box, and replace any contents with a value of your choosing.
  4. Click OK

New Name Dialog Box with Constant Value

You can use the Comment box (shown above) to provide a comment, which will display (below) when you select the Name in a formula.

Comment for Named Constant

Enter a Constant Value in a Name — Excel 2003, 2008

The menu path is: Insert, Name, Define…

Named Constant Menu Selection 2003

Excel 2003

In the Define Name dialog box:

  1. Enter a Name into: Names in workbook
  2. Click inside the Refers to text box, and replace any contents with a value of your choosing.
  3. Click Add
Add Constant Value with Name Box Excel 2008

Excel 2008

You can now use the Name in a formula and possibly freak somebody out.

Check to See if a Name Has a Constant Value

If you’re stumped as to where a constant value may be hidden, in Excel 2007 or 2010 check the Name Manager. Select Formulas, Name Manager or use the keyboard shortcut Ctrl+F3.

If there’s a value in the Refers To column, then you won’t find it in a worksheet cell.

Name Manager

Excel 2003 and 2008 don’t have a Name Manager. In Excel 2003 using the keyboard shortcut Ctrl+F3 brings up the Define Name dialog box. Similarly, in Excel 2008 Cmd+fn+F3 brings up the Define Name dialog box.

For these and earlier versions of Excel, you can get a freeware Name Manager here.

How to Make It Very Hard to Find a Name with a Constant Value

  1. Create a Name with a constant value
  2. Use VBA to change the Visible property of that Name to FALSE

Watch the confusion.

Related Posts Plugin for WordPress, Blogger...