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, … Read the rest

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 ItemsRead the rest

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
Read the rest

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:… Read the rest