Category and Item Table " data-medium-file="https://excelsemipro.com/wp-content/uploads/2011/05/Category-and-Item-Table.png" data-large-file="https://excelsemipro.com/wp-content/uploads/2011/05/Category-and-Item-Table.png" class="alignnone size-full wp-image-8016" title="Category and Item Table" src="https://excelsemipro.com/wp-content/uploads/2011/05/Category-and-Item-Table.png" alt="Category and Item Table" width="271" height="184" / 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. Create Category Defined Name " data-medium-file="https://excelsemipro.com/wp-content/uploads/2011/05/Create-Category-Defined-Name.png" data-large-file="https://excelsemipro.com/wp-content/uploads/2011/05/Create-Category-Defined-Name.png" class="alignnone size-full wp-image-8023" title="Create Category Defined Name" src="https://excelsemipro.com/wp-content/uploads/2011/05/Create-Category-Defined-Name.png" alt="Create Category Defined Name" width="297" height="221" / Data Validation…
World Possible is a nonprofit organization focused on connecting offline learners to the world's knowledge.
They work to ensure that anyone can access the best educational resources from the web anytime, anywhere, even if they do not have an internet connection.
We can create a second defined name that will give us a dynamic list with the exact count. =OFFSET(my Item List,0,0, COUNTA(my Item List),1) This formula uses the first defined name () but alters the height by counting the items.
I named this defined name my Item, as you can see below in the Name Manager screen shot.
Choose List and enter the following formula =my Item, then click OK.
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.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.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.