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)
There are two named ranges,
that refers to the range E1:E3 and
that refers to range F1:G3.
A defined name, myItemListH, … Read the rest
The 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… Read the rest
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()… Read the rest
Dim n As 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)
In the New Name dialog box:… Read the rest